Say I have a table defined as following:
CREATE TABLE my_tbl (
id bigint,
a1 bigint[],
a2 bigint[]
);
I would like to create a stored procedure to append values to a1 and a2. Is this the right way to proceed or is there a simpler way?
CREATE OR REPLACE FUNCTION append(
iid bigint,
next_a1 bigint,
next_a2 bigint)
DECLARE
r "my_tbl";
tmp_a1 bigint[];
tmp_a2 bigint[];
BEGIN
FOR r IN SELECT * FROM "my_tbl"
WHERE r."id" = iid
FOR UPDATE
LOOP
tmp_a1 := r.a1 || next_a1;
tmp_a2 := r.a2 || next_a2;
UPDATE my_tbl SET ( "a1", "a2" ) = ( tmp_a1, tmp_a2 )
WHERE "id" = iid;
END LOOP;
END; $$
LANGUAGE plpgsql;
I am on Postgresql 9.2.
