LISTAGG on svv_redshift_columns - Gives error because it can only be run on compute nodes - Looking for a workaround

1

I am trying to perform a LISTAGG on svv_redshift_columns (below query), however I receive the error 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.

From a StackOverflow post, I learned that it is probably because LISTAGG can only be used on compute nodes, and svv_redshift_columns is on the leader node.

With that said, what would be the best workaround? I am thinking a stored procedure may be the best option to return the results that I need.

Query

select
    schema_name,
    table_name,
    listagg(column_name, ', ') within group (order by ordinal_position) as columns_combined
from svv_redshift_columns
group by schema_name, table_name;
已提问 2 年前1504 查看次数
2 回答
1

no, you cannot create a user table based on svv_redshift_columns due to infamous "Specified types or functions (one per INFO message) not supported on Redshift tables" error. However, it works for svv_columns view. I am not sure what is the difference between these views except column names.

已回答 1 年前
0

If this is a one-time thing then you can create a user table with the data. Once you have such table you should be able to execute the listagg function.

profile pictureAWS
已回答 2 年前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则