Обсуждение: pg_background extension help
select * from testing('x') pv_result | pv_result2
-------------+-------------out value 1 | another out
I want to call this using pg_background and get the value of the two out parameters. Does anyone have any idea what I should use in the as section of the following query:
select * from pg_background_result(pg_background_launch('exec testing(''x'')')) as (???)
if I run something like:
vacuum verbose <my table>
I get 22 odd lines of guff and then a line with VACUUM on it.
If I run that in pg_background
SELECT * FROM pg_background_result(pg_background_launch('vacuum verbose <my table>')) as (result TEXT);
I get 22 odd lines of guff and then
-[ RECORD 1 ]--
result | VACUUM
(pv_result1 text, pv_result2 text) and even tried (result text, pv_result1 text, pv_result2 text) but no luck.That set me on a path of trying to work out what the remote query result rowtype is..
\df+ pg_background_result just tells me the result data type is "SETOF record"..
I tried the functions here: https://stackoverflow.com/questions/34883062/how-to-read-a-function-return-table-definition-from-pg-catalog-or-information-sc which work fine on my dummy procedure:
select proname, function_return_type_names(p)
from pg_proc p
where proname = 'testing';
-[ RECORD 1 ]--------------+------------------
proname | testing
function_return_type_names | {varchar,varchar}
All in all, I'm a bit lost at this point..
Any suggestions most welcome
Steve.
Stephen Anderson <stephen.anderson@appstech.com.au> writes: > I'm porting over an Oracle application to postgres and by and large its > been relatively seamless. One of the challenges I've hit is replicating > the functions/procedures that utilise Oracle's autonomous transaction > functionality. I've found Vibhor's pg_background extension ( > https://github.com/vibhorkum/pg_background) and successfully created it in > my instance and this seems pretty damn close to what I need. Unfortunately > I've hit a bit of a snag around getting parameter values back. So to the > point of all this :-) I know diddly-squat about pg_background, but judging from the symptoms you mention, it's not very smart about functions declared to return RECORD, which is what's happening under the hood if you use multiple OUT parameters. You might have better luck if you create a named composite type and declare the function to return that (which'll require some internal notational changes in the function, but nothing too awful). Or perhaps not. In any case, it'd be an idea to take this up with the extension's author; maybe it can be improved in future releases. regards, tom lane
Steve.
Stephen Anderson <stephen.anderson@appstech.com.au> writes:
> I'm porting over an Oracle application to postgres and by and large its
> been relatively seamless. One of the challenges I've hit is replicating
> the functions/procedures that utilise Oracle's autonomous transaction
> functionality. I've found Vibhor's pg_background extension (
> https://github.com/vibhorkum/pg_background) and successfully created it in
> my instance and this seems pretty damn close to what I need. Unfortunately
> I've hit a bit of a snag around getting parameter values back. So to the
> point of all this :-)
I know diddly-squat about pg_background, but judging from the symptoms
you mention, it's not very smart about functions declared to return
RECORD, which is what's happening under the hood if you use multiple OUT
parameters. You might have better luck if you create a named composite
type and declare the function to return that (which'll require some
internal notational changes in the function, but nothing too awful).
Or perhaps not. In any case, it'd be an idea to take this up with the
extension's author; maybe it can be improved in future releases.
regards, tom lane