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

[Sridhar N Bamandlapally <sridhar.bn1@xxxxxxxxx>]

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.g.johnston@xxxxxxxxx> wrote:

On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally <sridhar.bn1@xxxxxxxxx> 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.

​