gpt4 book ai didi

amazon-redshift - redshift - 如何将 listagg 与 information_schema.columns 表一起使用

转载 作者:行者123 更新时间:2023-11-29 11:15:04 27 4
gpt4 key购买 nike

我正在使用 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;

最佳答案

这是我测试过的解决方法..

select listagg(column_name, ', ') within group (order by column_name)
from
(
select
a.attname::varchar as column_name, typname::varchar as data_type
from
pg_type t,
pg_attribute a,
pg_class c,
pg_namespace ns,
(select top 1 1 from my_schema.my_table)
where
t.oid=a.atttypid
and a.attrelid = c.oid
and c.relnamespace = ns.oid
and typname NOT IN ('oid','xid','tid','cid')
and attname not in ('deletexid', 'insertxid')
and trim(relname) = 'my_table'
and ns.nspname = 'my_schema'
)

关于amazon-redshift - redshift - 如何将 listagg 与 information_schema.columns 表一起使用,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36783452/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com