Обсуждение: Newbie: help with FUNCTION
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? Thanks
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? > > How can I specify the names of the 5 output colums? > > Thanks > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
> > 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