Postgres query with variable in loop and condition on variable-CodePudding

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.

CodePudding user response：

(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.