in reply to Returning multiple rows from pgplsql to DBI

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

[download]

Replies are listed 'Best First'.
Re^2: Returning multiple rows from pgplsql to DBI
by anonymized user 468275 (Curate) on May 30, 2018 at 13:12 UTC
    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

[reply]

      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]

In Section Seekers of Perl Wisdom