Question 115069 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – FOR loop with the table name as a variable

I am trying to create a FOR loop to loop through an array list of table names.

Something like

list=['tableA','tableB','tableC'];
for x in list{
  select * from x;
}

basically doing this

select * from tableA;
select * from tableB;
select * from tableC;

This is what I’ve gotten so far.

DO $$
DECLARE 
    dataTable TEXT;
    countData integer;
BEGIN
FOREACH dataTable IN ARRAY ARRAY['tableA','tableB','tableC'] 
    LOOP
        RAISE NOTICE 'hello %', dataTable;
--      EXECUTE 'select count(*) from' || quote_ident(dataTable);
        select count(*) into countData from quote_ident(dataTable);
        RAISE NOTICE 'count %', countData;
    END LOOP;
END; $$;

Which results in

NOTICE:  hello tableA
NOTICE:  count 1
NOTICE:  hello tableB
NOTICE:  count 1
NOTICE:  hello tableC
NOTICE:  count 1
DO

(not sure where the DO is coming from and why the value of dataCount is 1. All the tables listed has data; more than 1 row. )

Im using SELECT as a test, but my endgoal is basically trying to execute SQL statements in a FOR loop with the table names being variables.(so, instead of SELECT, I want to use…say INSERT or DELETE)

I saw a few solutions that uses the table INFORMATION_SHCEMA or something but this is not what Im looking for.

Tags:

2

Answers


  1. 0

    Uncomment this line

    EXECUTE 'select count(*) from' || quote_ident(dataTable);

    Add space between from and single quote and change the query to

    EXECUTE 'select count(*) from ' ||dataTable INTO countData;

    Comment the line below

    --select count(*) into countData from quote_ident(dataTable);

    The reason you get 1 every time because in your query, if you pass any string variable in quote_indent() it will return 1 try this execute 'SELECT count(*) FROM '||quote_ident('xyz'); and the result will be the same, this means instead of treating the value in a variable as a table name it is treating it as a plain string only.

    Login or Signup to reply.
  2. 0 
    DO $$
DECLARE 
    dataTable TEXT;
    countData integer;
BEGIN
FOREACH dataTable IN ARRAY ARRAY['int2_tbl','int4_tbl','int8_tbl'] 
    LOOP
        RAISE NOTICE 'hello %', dataTable;
        EXECUTE 'select count(*) from ' || quote_ident(dataTable) into countData;
      
        RAISE NOTICE 'count %', countData;
    END LOOP;
END; $$;

    from here need extra space, otherwise string will be concatenate together.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search