|Just another Perl shrine|
Returning multiple rows from pgplsql to DBIby (anonymized user) (Curate)
|on May 30, 2018 at 10:26 UTC||Need Help??|
(anonymized user) has asked for the wisdom of the Perl Monks concerning the following question:
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.;
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:
and the perl...
for two rows of test data returned, this was formatted as:
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.
One world, one people