Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Returning multiple rows from pgplsql to DBI

by (anonymized user) (Curate)
on May 30, 2018 at 10:26 UTC ( #1215439=perlquestion: print w/replies, xml ) Need Help??

(anonymized user) has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I am building a system in Perl, DBD::Pg and PostgreSQL. I have written about 35 stored procedures, each of which has an interface in my database access module. Until now, all stored procedures return a single result. plpgsql functions don't like targetless selects and I don't want to write select queries at the perl end, because the server is destined to be execute-only for security reasons. I tried ending a function with RETURN SELECT ... but that isn't permitted either, even though it provides the select with a target. (e.g. IF SELECT ... satisfies the target requirement and so is accepted by plpgsql and works ok.) Each procedure has an interface method in my Perl DB module, e.g.;
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; }
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!

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:

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';
and the perl...
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; }
for two rows of test data returned, this was formatted as:
$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")' ];
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:

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 (Parson) on May 30, 2018 at 13:28 UTC

    I discovered a postgres bug in rowtype

    If you can explain what the bug is I'll have it fixed. (Please mention what platform and software versions are used)

      In the below code, the FOR doc IN SELECT ... loop was returning a NULL in doc_typeid - impossible because it has a foreign key constraint on it. By luck I found that modifying it to FOR doc in SELECT doc_id, doc_typeid magically forced the correct value into doc_typeid (luck because I didn't need to select doc_id). When I changed the declaration of doc to record instead of a rowtype and removed the kludgey doc_id from the SELECT, it also worked correctly. These different tests led me to conclude that something other than the list of selected columns in the SELECT was determining the result set - perhaps the columns as ordered in the doc table at the last creation or alter table event are driving the population of the rowtype variable instead. So perhaps because record does not depend on any tables, my workaround to replace the rowtype declaration with record worked first time.
      CREATE OR REPLACE FUNCTION public."doct_GetActMask"( parm_holid bigint, parm_holtype character varying) RETURNS integer AS $BODY$DECLARE doc document%ROWTYPE;; actmask integer := 0; doctpid integer := NULL; approved integer := NULL; verified integer := NULL; logok integer := NULL; BEGIN SELECT docs_id FROM public."DocumentStatus" WHERE docs_shortname = 'Approved' INTO approved; SELECT docs_id FROM public."DocumentStatus" WHERE docs_shortname = 'Verified' INTO verified; FOR doc IN SELECT doc_type_id FROM document WHERE doc_hol_id = parm_holid AND (doc_docsid = approved OR doc_docsid = verified) LOOP SELECT public."doct_GetRoot"(doc.doc_type_id) INTO doctpid; IF doctpid < 0 THEN RETURN doctpid; END IF; actmask := actmask | (1<<(doctpid-1)); END LOOP; RETURN actmask; END$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION public."doct_GetActMask"(bigint, character varying) OWNER TO mydbo;

      One world, one people

Re: Returning multiple rows from pgplsql to DBI
by Anonymous Monk on May 30, 2018 at 11:33 UTC

    What's wrong with fetching results row-by-row in a loop?

    Also, sprintf doesn't sanitize input. Why not use the safe variable binding supplied by DBI itself?

    my $sth = $dbh->prepare('SELECT public."hol_Suff"(?)'); $sth->execute($parm{holid});
      In the example method shown so far, there is always only one row. In the new requirement, which I now have to update in the OP to show the new method, yes I do fetch them in a loop. The DBI safe variable binding does not apply to parameters of a function call.

      One world, one people

        Untested but try SELECT * FROM function() with sort on the results set (not in the function). For example

        sub mmgb_getAllBooks { my $self = shift; my $dbh = $self->dbh; my %parm = @_; my $p1 = $parm{mmgr_shortname} ? "character varying '$parm{mmgr_shor +tname}'" : 'NULL'; my $p2 = $parm{direction} ||= 'ASC'; my $sql = "SELECT * FROM mmgb_getAllBooks($p1) ORDER BY c7 $p2"; my $sth = $dbh->prepare($sql); $sth->execute(); return $sth->fetchall_arrayref(); }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1215439]
Approved by marto
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (3)
As of 2020-08-12 03:24 GMT
Find Nodes?
    Voting Booth?
    Which rocket would you take to Mars?

    Results (64 votes). Check out past polls.