How can I run a postgresql query over array of strings?

Question

I'm writing a script which will take all tables from my schema and perform some actions on them. The tables that will be taken have same prefix and different suffix. Now, what I want to do is to declare an array at the beginning of the script, the array will contain all regex for all tables that I need, for example:

base_tables varchar[2] := ARRAY['table_name_format_2%',
                            'another_format_3%'];

Using this array, I would like to go through all the tables in my schema and take only those that match the name pattern in the array. I tried to do this as such:

FOR table_item IN
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_name LIKE IN base_tables
    LOOP 
        ---- Some code goes here -----
    END LOOP;

The error I get is :

ERROR: syntax error at or near "IN"

What is the correct way to compare each table name, to the names in my array?

Thanks in advance.

S-Man · Accepted Answer · 2018-12-11 15:34:25Z

1

demo:db<>fiddle

To get a match for an array element you have to use:

-- general case
WHERE element = ANY(ARRAY['elem1', 'elem2'])

-- your case
WHERE table_name = ANY(base_tables)

If you want to achieve a LIKE eehrm... like operation you'll need another way:

SELECT table_name 
FROM information_schema.tables t
JOIN (SELECT unnest(base_tables) as name) bt
ON t.table_name LIKE bt.name

Joining tables against an unnested base_tables array (unnest expands an array to one row for each element). You can join with the LIKE operator.

demo:db<>fiddle

edited Dec 11, 2018 at 15:34

answered Dec 11, 2018 at 14:40

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How can I run a postgresql query over array of strings?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related