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;
}
[download]

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';
[download]

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;
}
[download]

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")'
        ];
[download]

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.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.