in reply to Re: Returning multiple rows from pgplsql to DBI
in thread Returning multiple rows from pgplsql to DBI

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

  • Comment on Re^2: Returning multiple rows from pgplsql to DBI

Replies are listed 'Best First'.
Re^3: Returning multiple rows from pgplsql to DBI
by poj (Abbot) on May 30, 2018 at 19:01 UTC

    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();
}

    [download]
    poj
[reply]
[d/l]
[select]
      If it works, wouldn't it be the same as RETURN QUERY SELECT ...? At some point I also have to restrict access to function calls and SELECT etc. will become forbidden for security reasons.

      One world, one people

[reply]
        I don't want to write select queries at the perl end

        How will you call the plpgsql function in your module ?

        poj
[reply]

In Section Seekers of Perl Wisdom