Listagg Redshift DDL

Question

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'

It's giving me this error:

Query execution failed

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?

Answer 1

listagg function is a compute-node only function.

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

According to AWS documentation

A query that references only catalog tables (tables with a PG prefix, such as PG_TABLE_DEF) or that does not reference any tables, runs exclusively on the leader node.

If a query that uses a compute-node function doesn't reference a user-defined table or Amazon Redshift system table returns the following error.

[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/latest/dg/c_SQL_functions_compute_node_only.html

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