帶有回圈變數和變數條件的Postgres查詢

我有一個查詢，它根據變數old_id和new_id. 但條件是我需要動態獲取變數。這是我正在使用的簡單查詢。

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
$$;

我需要動態分配old_id和new_id

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
$$;

預期的問題陳述（分配變數并在進一步執行中使用它）在注釋中。

uj5u.com熱心網友回復：

(a) 在您的第一個“簡單查詢”中，表的更新poets可以由表上定義的觸發器函式自動執行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 () ;

您的第一個查詢可以簡化為：

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) 可以使用sql查詢而不是plpgsql回圈來執行表更新，并且性能更好：

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 ;
$$;

此解決方案尚未經過測驗，可能必須進行調整才能正常作業。請在dbfiddle 中提供表定義songs和poets資料示例，以便我可以測驗和調整建議的解決方案。

標籤：PostgreSQL的

上一篇：PostgreSQL默認結果限制

下一篇：如何在SQLAlchemy（特別是Flask-SQLAlchemy）中使用PostgreSQL擴展？