I'm trying to create a function that takes 1 paramater (eg an integer) as input, then does 5 database updates or deletes in 5 different SQL statements and returns 5 integers (in one resultset) indicating how many rows were affected by the various updates/deletes.
How do I do this?
How can I specify the names of the 5 output colums?
Why not return 5 rows instead. That way you can extend it to 6 queries easily.
CREATE TYPE num_rows_affected AS ( tbl_name text, num_rows int4 );
CREATE FUNCTION do_stuff(int4) RETURNS SETOF num_rows_affected AS ' DECLARE res num_rows_affected; BEGIN -- Do query 1 here GET DIAGNOSTICS res.num_rows := ROW_COUNT; res.tbl_name := ''table1''; RETURN NEXT res; -- Do query 2 here GET DIAGNOSTICS res.num_rows := ROW_COUNT; res.tbl_name := ''table1''; RETURN NEXT res; ...etc... RETURN; END; ' LANGUAGE plpgsql;
SELECT * FROM do_stuff(123);
Full details in the plpgsql chapter of the manuals. None of the above is tested for syntax errors. You can use block-quoting in version 8.0
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
