Re^3: Returning multiple rows from pgplsql to DBI

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

Comment on Re^3: Returning multiple rows from pgplsql to DBI Select or Download Code

Replies are listed 'Best First'.
Re^4: Returning multiple rows from pgplsql to DBI by anonymized user 468275 (Curate) on May 31, 2018 at 22:31 UTC
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]
Re^5: Returning multiple rows from pgplsql to DBI by poj (Abbot) on Jun 01, 2018 at 09:33 UTC
I don't want to write select queries at the perl end How will you call the plpgsql function in your module ? poj	[reply]
Re^6: Returning multiple rows from pgplsql to DBI by anonymized user 468275 (Curate) on Jun 04, 2018 at 16:27 UTC
There are various ways to call a function without using select, e.g. assignment and PERFORM. Although I may be constrained for the case of returning multiple values (although at some point I will still try to get around that) One world, one people	[reply]