Listagg Redshift DDL [英] Listagg Redshift DDL

问题描述

我正在尝试在Redshift中检索表的DDL.我发现此视图，在这里我可以轻松地为任何表选择定义.但是我需要一行信息，而且我知道有这个Listagg函数，但是如果我尝试这样做:

I am trying to retrieve the DDL for a table in Redshift. I found this view where I can easily select the definition for any table. However I need this information in one line, and I know that there is this Listagg function, but if I try to do this:

select listagg(ddl, ' ')
from admin.v_generate_tbl_ddl
where schemaname = 'schema'
and tablename = 'orders'

这给了我这个错误:

查询执行失败

原因:SQL错误[XX000]:错误:一个或多个已使用的函数必须应用于至少一个用户创建的表.用户实例表中唯一的函数是LISTAGG，MEDIAN，PERCENTILE_CONT等

Reason: SQL Error [XX000]: ERROR: One or more of the used functions must be applied on at least one user created tables. Examples of user table only functions are LISTAGG, MEDIAN, PERCENTILE_CONT, etc

您能帮助我如何实现这一目标吗?

Can You please help me on how can I achieve this?

推荐答案

listagg 函数是仅计算节点函数.

但是您为获取表ddl而运行的查询仅在Leader上运行，因为它仅指定pg_ *表.

But the query you run to get the table ddl runs only on leader because it only specifies pg_* tables.

根据AWS文档

仅引用目录表(带有PG前缀的表，例如PG_TABLE_DEF)的查询，或者不引用任何表的查询，仅在领导节点上运行.

如果使用计算节点功能的查询未引用用户定义的表或Amazon Redshift系统表，则会返回以下错误.

[Amazon](500310)无效的操作:必须在至少一个用户创建的表上应用一个或多个已使用的函数.

[Amazon] (500310) Invalid operation: One or more of the used functions must be applied on at least one user created table.

https://docs.aws.amazon.com/redshift/最新/dg/c_SQL_functions_compute_node_only.html

总而言之，由于您的查询未引用任何用户创建的表，因此您不能使用 listagg

To sum up, since your query does not reference any user created table, you can not use listagg

这篇关于Listagg Redshift DDL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！