Pass VARIADIC argument into IN in Postgres

Question

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?

`table_name IN (list)` change to `table_name = ANY (list)` – Vao Tsun Jan 11 '18 at 14:21 — Vao Tsun, Jan 11 '18 at 14:21
Daym!, that works. Post it as an answer so I can accept – simPod Jan 11 '18 at 14:23 — simPod, Jan 11 '18 at 14:23

score 2 · Accepted Answer · edited Jan 12 '18 at 14:27

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

edited Jan 12 '18 at 14:27

Erwin Brandstetter

605,456
145
1,078
1,228

answered Jan 11 '18 at 14:25

Vao Tsun

47,234
13
100
132

Thank you @Erwin Brandstetter – Vao Tsun Jan 12 '18 at 14:31

Pass VARIADIC argument into IN in Postgres

1 Answers1