How to update array fields in a table in Postgresql?

Question

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.

Answer 1

You can simply do

UPDATE my_tbl 
   SET (a1, a2) = (a1 || 123465, a2 || 132456789)
 WHERE id = 321;

To obtain a function, you simply have to replace the integer constants with the parameters. You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.