CODEWITHSUNDEEP
sqlpostgresqlplpgsql
simPod
simPod

Reputation: 13456

Pass VARIADIC argument into IN in Postgres

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?

Upvotes: 1

Views: 1352

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

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

Upvotes: 2

Related Questions