我正在使用redshift,并希望创建一个以逗号分隔的列列表.我正在尝试使用listagg从信息模式中获取列名：
SELECT listagg(column_name,',') within group (order by ordinal_position) FROM information_schema.columns WHERE table_schema = 'my_schema' AND table_name = 'my table';
我收到以下错误：
[Amazon](500310) Invalid operation: Function (listagg(text,text)) must be applied on at least one user created tables;
虽然这不能回答如何在information_schema上应用listagg,但我可以推荐另一种在pg目录表上使用listagg的方法.
试试这个：
SELECT DISTINCT listagg(attname,') WITHIN GROUP (ORDER BY a.attsortkeyord) AS "columns"
FROM pg_attribute a,pg_namespace ns,pg_class c,pg_type t,stv_tbl_perm p,pg_database db
WHERE t.oid=a.atttypid AND a.attrelid=p.id AND ns.oid = c.relnamespace AND db.oid = p.db_id AND c.oid = a.attrelid
AND typname NOT IN ('oid','xid','tid','cid')
AND ns.nspname = 'my_schema'
AND RTRIM(name) = 'my table'
