Charl Gerber wrote:
> 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
