Re^2: 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]
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


P is for Practical
	PerlMonks