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

Alternatives to returning a set of rows is to return
a set of integers, one per row selected. Same example
as Huxton's without the row type.

Also you could return an array of integers.

--elein

On Thu, Feb 24, 2005 at 09:00:46PM +0000, 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?

You could return an array like elein recommended....

How can I specify the names of the 5 output colums?

but you won't have "names" associated with the output columns. To have
something other than integers as the index (such as a string), you
need to use an associative array.

sub foo
{
my $integer = shift;

my $ret1 = $dbh->do('some sql here');

my $ret2 = $dbh->do('some sql here');

# etc.... for 5 statements.

my %hash = (
'Column Label 1' => $ret1,
'Column Label 2' => $ret2,
#..... etc...
);

return \%hash;
}

So, you should read about associative arrays (aka hashes) and references.

Rick