Creating temporary table dynamic with loop for in PostgreSQL

Question

I am writing a script in postgresql for the creation of dynamic temporary tables, both for the name of the table and for the condition where a.state =. The following query fulfills this objective, since it replaces the string ('ACTIVE') of the variable declared level in the query that is initialized with the begin.

DO $$
declare 
   level varchar := 'ACTIVE';
BEGIN
 execute('drop table if exists con_users_'|| level ||
'; create temp table con_users_'|| level ||' AS (
SELECT * FROM usuario a WHERE a.state = '''|| level  ||''')');
END$$

However, the problem arises when I want to add the for loop to traverse an array ['ACTIVE', 'OFF'] in order to independently execute both conditions in the variable level. Get this forward:

      DO $$
      declare 
      i  varchar[];
      level varchar[] := array['ACTIVE','OFF'];   

       begin
         for i  in  level

         loop

   execute('drop table if exists con_users_'|| level ||
   '; create temp table con_users_'|| level ||' AS (
   SELECT * FROM users a WHERE a.state = '''|| level  ||''')');

        end loop;

 END$$

ERROR: syntax error at or near "level" LINE 8: for i in level ^ SQL state: 42601 Character: 230

My goal is that with an execution of the script I create two temporary tables with name and where condition according to the list of the array ['ACTIVE', 'OFF']. I require this code for a development with a more complex database, but if it works with this example base it is enough

CREATE TABLE IF NOT EXISTS users
(
    users_id serial primary key,
    users    varchar(50),
    state    varchar(7)
);

INSERT INTO users(users_id, users, state)
VALUES (1, 'Peter', 'ACTIVE'),
       (2, 'Gerard', 'OFF'),
       (3, 'Juna', 'ACTIVE');

Jim Jones · Accepted Answer · 2021-07-06 05:12:35Z

1

You need to iterate over the array in your loop:

DO $$
DECLARE
  i  varchar[];
  level varchar[] := array['ACTIVE','OFF'];   

BEGIN
 FOR i IN 1..array_length(level,1) LOOP
  EXECUTE('drop table if exists con_users_'|| level[i] ||
          '; create temp table con_users_'|| level[i] ||' AS (
          SELECT * FROM users a WHERE a.state = '''|| level[i]  ||''')');

 END LOOP;
END$$

Demo: db<>fiddle

edited Jul 6, 2021 at 5:12

answered Jun 25, 2021 at 8:32

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

1 Comment

Amrmsmb Over a year ago

hi, could you please answer this question:stackoverflow.com/questions/68264609/…

Collectives™ on Stack Overflow

Creating temporary table dynamic with loop for in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related