1

I'm trying to build a Postgres function that takes table names as variadic argument and outputs the query, that returns select string from information_schema like

SELECT *
FROM table1 
UNION 
SELECT *
FROM table2

So far I made following:

CREATE OR REPLACE FUNCTION query_tables(VARIADIC list text[])
    RETURNS text
AS $$
DECLARE
    qry TEXT;
BEGIN
    SELECT string_agg(
        format('SELECT *
            FROM %1$I', table_name),
        ' UNION ')
    INTO qry
    FROM information_schema.tables
    WHERE table_schema = 'public'
          AND table_name IN (list);

    return qry;
END;
$$

So it can be called like this SELECT * FROM query_tables('table1','table2');

However, I'm stuck at IN (list) where I'm not able to pass that variadic argument in IN().

Is it possible?

2
  • 2
    table_name IN (list) change to table_name = ANY (list) Commented Jan 11, 2018 at 14:21
  • Daym!, that works. Post it as an answer so I can accept Commented Jan 11, 2018 at 14:23

1 Answer 1

2

To use an array, change

table_name IN (list) to table_name = ANY (list)

https://www.postgresql.org/docs/current/static/functions-comparisons.html

expression operator ANY (array expression)

Also: https://stackoverflow.com/a/31192557/5315974

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you @Erwin Brandstetter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.