CODEWITHSUNDEEP
sqlamazon-redshift
Bunyk
Bunyk

Reputation: 8067

How to put redshift query in function?

It is very hard to me to remember query for listing all tables in redshift:

SELECT DISTINCT tablename
FROM pg_table_def
WHERE schemaname = 'public'
ORDER BY tablename;

So I decided to create function that does it to me. But it turns out redshift does not understand "RETURNS TABLE" statement:

ERROR:  syntax error at or near "TABLE"
LINE 2: RETURNS TABLE (tablename varchar)
            ^

I tried with other return types 

CREATE FUNCTION show_tables()
RETURNS ANYELEMENT VOLATILE
AS 'SELECT DISTINCT tablename
    FROM pg_table_def
    WHERE schemaname = public
    ORDER BY tablename;'
LANGUAGE SQL;

But I get error ERROR: anyelement is not a supported sql UDF return type.

So, my question: "Is there any way to create show_tables() function in redhsift?"

Upvotes: 0

Views: 3012

Answers (2)

Harsha TJ
Harsha TJ

Reputation: 272

The Create Function comes with a $$ notation (Dollar Notation). Please check the AWS Documentation.

For this I Hope the following helps :

CREATE FUNCTION show_tables(Some arguments here if required)
RETURNS ANYELEMENT VOLATILE 
AS $_$ SELECT DISTINCT tablename
    FROM pg_table_def
    WHERE schemaname = public
    ORDER BY tablename
    $_$
LANGUAGE sql;

Upvotes: 0

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

If your return type is anyelement, atleast one of your input parameters must be anyelement.

For reference, visit the relevant section (towards the bottom of the page) at Amazon Redshift Documentation

Upvotes: 1

Related Questions