Re: [HACKERS] OUT parameter and RETURN table/setof

Existing application code written to call function in Oracle which return
no.of rows in out parameter and return-values is cursor-result

this need migrate to PostgreSQL, need help here

example: (actual function declaration only)
*Oracle:*
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR2,
v_rowsfound OUT INTEGER,
result_cursor1 OUT SYS_REFCURSOR
) ...

*PostgreSQL:*
*method 1*:
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR,
v_rowsfound OUT INTEGER,
result_cursor1 OUT REFCURSOR
) ...

but this approach issue is, need to do in BEGIN - END block inside
with FETCH ALL IN "<unnamed portal X>"
- here we need/think common approach for database

*method 2:*
CREATE OR REPLACE PROCEDURE sc_getapppermissionlist (
v_role_ids IN VARCHAR,
v_rowsfound OUT INTEGER)
RETURNS TABLE/SETOF
...

this approach is not working

Thanks
Sridhar
OpenText

On Mon, Jun 6, 2016 at 5:57 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally <
> sridhar(dot)bn1(at)gmail(dot)com> wrote:
>
>> Hi
>>
>> Is there any option in PGPLSQL which can RETURNS table or SETOF rows
>> along with an OUT parameter?
>>
>>
> ​No, there would be no point given the internals of how functions work.
>
> ​What is it you are trying to do?
>
> David J.
> ​
>
>