CODEWITHSUNDEEP
sqlarraysdatabasepostgresqlplpgsql
Jean
Jean

Reputation: 33

Creating temporary table dynamic with loop for in PostgreSQL

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');

Upvotes: 1

Views: 1486

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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

Upvotes: 1

Related Questions