anonymized user 468275 has asked for the wisdom of the Perl Monks concerning the following question:
But the 36th procedure I have to write needs to return multiple rows. My first idea (untested) is to declare the function to return type "record []", but I feel that is likely to conflict with what DBI expects. Anyone know the proper way to return a result set in a Perl DBI <-> plpgsql function interface? I have been searching for it here with super search plus all the usual suspects (stack overflow etc.) but so far without success. Many thanks in advance!sub hol_Suff { my $self = shift; my %parm = @_; $parm{holid} or die; my $dbh = $self->dbh; my $sth = $dbh->prepare( sprintf( 'SELECT public."hol_Suff"(%s)', "$parm{holid}" ) ); $sth->execute; my ($actmask) = $sth->fetchrow_array; return $actmask; }
Update: My second, more reasonable idea, having dug deeper with google, which could potentially be the proper way, is to use the RETURN QUERY syntax
More update: the documentation example suggests configuring the function to return SETOF + rowtype. Unfortunately, I discovered a postgres bug in rowtypes (probably it returns columns in table order, ignoring the SELECT column order, SEP, so dump using rowtypes) and so I have converted all rowtype declations into record declarations which worked around the bug. This means I have to declare RETURN SETOF record for the function. It remains to be seen whether DBD::Pg DWIMS on that one, given that most of the world uses standard SQL.
More more update. SETOF record wasn't allowed but RETURN TABLE + description of record did work to a point. But as feared, DBI did not know how to cope with it and returned each row as a comma separated string. Here are the new postgres function creation SQL just written and perl interface method that got me to this new point:
and the perl...CREATE OR REPLACE FUNCTION public."mmgb_getAllBooks"( IN parm_moneymgr character varying, IN parm_sortorder character varying DEFAULT 'ASC'::character varyi +ng) RETURNS TABLE(c1 bigint, c2 character varying, c3 character varying, + c4 character varying, c5 character varying, c6 real, c7 timestamp wi +thout time zone) AS $BODY$DECLARE mmgr_flag boolean := NULL; asc_flag boolean := true; BEGIN mmgr_flag = (parm_moneymgr IS NOT NULL); asc_flag = (NOT parm_sortorder = 'DESC'); RETURN QUERY SELECT b.mmgb_id, i.instt_abbr, i.instt_shortname, c.cur_iso, b.mmgb_shortname, b.mmgb_weight, b.mmgb_infodate FROM mmgr_book b, public."InstrumentType" i, currency c, public."MoneyMgr" m, mmgr_status s WHERE b.mmgb_insttid = i.instt_id AND (m.mmgr_shortname = parm_moneymgr OR NOT mmgr_flag) AND b.mmgb_curid = c.cur_id AND s.mmgs_id = m.mmgr_statid AND s.mmgs_shortname = 'Active' AND m.mmgr_id = b.mmgb_mmgrid ORDER BY asc_flag, CASE WHEN asc_flag THEN b.mmgb_infodate END ASC, CASE WHEN NOT asc_flag THEN b.mmgb_infodate END DESC; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000; ALTER FUNCTION public."mmgb_getAllBooks"(character varying, character +varying) OWNER TO mydbo; COMMENT ON FUNCTION public."mmgb_getAllBooks"(character varying, chara +cter varying) IS 'optional filters to cater for known use cases';
for two rows of test data returned, this was formatted as:sub mmgb_getAllBooks { my $self = shift; my %parm = @_; $parm{direction} ||= 'ASC'; my $dbh = $self->dbh; my $sth = $dbh->prepare( sprintf ('SELECT public."mmgb_getAllBooks"(%s, %s)', $parm{mmgr_shortname} ? "character varying '$parm{mmgr_shortna +me}'" : 'NULL', "character varying '$parm{direction}'" ) ); $sth->execute; my @return; while (my @row = $sth->fetchrow_array) { push @return, @row; # got rid of \@row because each row is returne +d as a single string it seems } return \@return; }
The per column formatting is pretty arbitrary, but the underlying problem is probably that DBI expects standard SQL instead of plpgsql. For the time being I'll live with having to decode this unless someone has a better way. More more more update:$VAR1 = [ '(17,SPX,"S&P 500 Index",USD,"S&P 500",50,"2017-06-15 15:06: +47")', '(19,FTSE100,"FTSE 100 Index",GBP,"FTSE 100",18,"2017-07-01 +15:06:47")' ];
I finally found that the way to receive multiple values whether returned as a record or a table is to select * from the function call.
update: and some time after that I favoured using user-defined records (create type in postgres) which forces an expectable set of components to be passed or returned.
One world, one people
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Returning multiple rows from pgplsql to DBI
by erix (Prior) on May 30, 2018 at 13:28 UTC | |
by anonymized user 468275 (Curate) on May 30, 2018 at 13:52 UTC | |
Re: Returning multiple rows from pgplsql to DBI
by Anonymous Monk on May 30, 2018 at 11:33 UTC | |
by anonymized user 468275 (Curate) on May 30, 2018 at 13:12 UTC | |
by poj (Abbot) on May 30, 2018 at 19:01 UTC | |
by anonymized user 468275 (Curate) on May 31, 2018 at 22:31 UTC | |
by poj (Abbot) on Jun 01, 2018 at 09:33 UTC | |
|