Re: Newbie: help with FUNCTION

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