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

0

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;
asked 10 months ago280 views
1 Answer
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.

answered 8 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions