Postgres query with variable in loop and condition on variable

Question

I have a query which updates the records based on variables old_id and new_id. But condition is I need to fetch the variables dynamically. Here is simple query which I am using.

do
$$
    declare
        old_id         bigint = 1561049391647687270;
        declare new_id bigint = 2068236279446765699;
    begin
        update songs set poet_id = new_id where poet_id = old_id;
        update poets set active = true where id = new_id;
        update poets set deleted = true where id = old_id;
    end
$$;

I need to assign the old_id and new_id dynamically


do
$$
    declare
        su     record;
        pc     record;
        old_id bigint;
        new_id bigint;
    begin
        for pc in select name, count(name)
                  from poets
                  where deleted = false
                  group by name
                  having count(name) > 1
                  order by name
            loop
                for su in select * from poets where name ilike pc.name
                    loop
                        -- old_id could be null where I have 2 continue the flow without update
                        for old_id in (select id from su where su.link is null)
                            loop
                                raise notice 'old: %', old_id;
                            end loop;
                        -- new_id could be more than 2 skip this condition as well
                        for new_id in (select id from su where su.link is not null)
                            loop
                                raise notice 'new: %', new_id;
                            end loop;
                    end loop;
                -- run the statement_1 example if new_id and old_id is not null
            end loop;
    end
$$;

The expected problem statement (to assign variable and use it in further execution) is with in comment.

Edouard · Accepted Answer · 2022-01-01 13:12:57Z

(a) In your first "simple query", the update of the table poets could be automatically executed by a trigger function defined on the table songs :

CREATE OR REPLACE FUNCTION songs_update_id ()
RETURNS trigger LANGUAGE plpgsql AS
$$
BEGIN
UPDATE poets SET active = true WHERE id = NEW.poet_id ;
UPDATE poets SET deleted = true WHERE id = OLD.poet_id ; -- SET active = false to be added ?
END ;
$$ ;

CREATE OR REPLACE TRIGGER songs_update_id AFTER UPDATE OF id ON songs
FOR EACH ROW EXECUTE songs_update_id () ;

Your first query can then be reduced as :

do
$$
    declare
        old_id         bigint = 1561049391647687270;
        declare new_id bigint = 2068236279446765699;
    begin
        update songs set poet_id = new_id where poet_id = old_id;
    end
$$;

(b) The tables update could be performed with a sql query instead of a plpgsql loop and with better performances :

do
$$
BEGIN
UPDATE songs
   SET poet_id = list.new_id[1]
FROM
(   SELECT b.name
         , array_agg(b.id) FILTER (WHERE b.link IS NULL) AS old_id
         , array_agg(b.id) FILTER (WHERE b.link IS NOT NULL) AS new_id
      FROM 
         ( SELECT name
             FROM poets
            WHERE deleted = false
            GROUP BY name
           HAVING COUNT(*) > 1
--          ORDER BY name   -- this ORDER BY sounds like useless and resource-intensive
         ) AS a
     INNER JOIN poets AS b
        ON b.name ilike a.name
     GROUP BY b.name
    HAVING array_length(old_id, 1) = 1
       AND array_length(new_id, 1) = 1
) AS list
WHERE poet_id = list.old_id[1] ;
END ;
$$;

This solution is not tested yet and could have to be adjusted in order to work correctly. Please provide the tables definition of songs and poets and a sample of data in dbfiddle so that I can test and adjust the proposed solution.

Collectives™ on Stack Overflow

Postgres query with variable in loop and condition on variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related