postgresql – redshift – 如何将listagg与information_schema.columns表一起使用

前端之家收集整理的这篇文章主要介绍了postgresql – redshift – 如何将listagg与information_schema.columns表一起使用前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在使用redshift,并希望创建一个以逗号分隔的列列表.我正在尝试使用listagg从信息模式中获取列名:
  1. SELECT
  2. listagg(column_name,',') within group (order by ordinal_position)
  3. FROM information_schema.columns
  4. WHERE table_schema = 'my_schema'
  5. AND table_name = 'my table';

我收到以下错误

  1. [Amazon](500310) Invalid operation: Function (listagg(text,text)) must be applied on at least one user created tables;
虽然这不能回答如何在information_schema上应用listagg,但我可以推荐另一种在pg目录表上使用listagg的方法.

试试这个:

  1. SELECT DISTINCT listagg(attname,') WITHIN GROUP (ORDER BY a.attsortkeyord) AS "columns"
  2. FROM pg_attribute a,pg_namespace ns,pg_class c,pg_type t,stv_tbl_perm p,pg_database db
  3. 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
  4. AND typname NOT IN ('oid','xid','tid','cid')
  5. AND ns.nspname = 'my_schema'
  6. AND RTRIM(name) = 'my table'

猜你在找的Postgre SQL相关文章