What is the result rowtype from a call to a procedure with multiple out parameters?

[sa73917]

Hi Vibhor, At best this is a documentation issue but more likely it's just my lack of understanding of postgres but as I've killed a few hours googling without success I figure it's perhaps something worth including in the README.md for anyone else who hits this query :-)..

If I have simple procedure that accepts a single parameter and returns two values in out parameters. Eg.

select * from testing('x')
  pv_result  | pv_result2  
-------------+-------------
 out value 1 | another out

what should I use in the as section of the following query:

select * from pg_background_result(pg_background_launch('exec testing(''x'')')) as (???)

I've tried the suggested (result text) and tried matching my out variables (pv_result1 text, pv_result2 text) and even tried (result text, pv_result1 text, pv_result2 text) but unfortunately all are met with :

ERROR: remote query result rowtype does not match the specified FROM clause rowtype

Is there any way for me to know what the remote query result rowtype is? Any suggestions most welcome.

[rjuju]

Hi,

Sorry for the late answer.

You can't use an anonymous record type as far as I can tell. Using a custom type should work fine. Here is an example on how to do so:

CREATE TYPE t_testing AS (v1 text, v2 text);
CREATE TYPE

CREATE FUNCTION testing() RETURNS SETOF t_testing AS $$
SELECT 'v1', 'v2';
$$ LANGUAGE sql;
CREATE FUNCTION

SELECT (res).* FROM pg_background_result(pg_background_launch($$select t from testing()t $$)) AS v(res t_testing);
 v1 | v2 
----+----
 v1 | v2
(1 row)

[salimepoint]

Thanks @rjuju - had forgotten I'd raised this one :laughing: - but good to know for future reference!

[rjuju]

Thanks for the feedback @salimepoint !