From: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> |
---|---|
To: | Mukesh Rajpurohit <vivasvan1902(at)gmail(dot)com> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Multiple refcursor as INOUT parameter in procedure |
Date: | 2021-07-22 12:47:19 |
Message-ID: | CAM+6J96nRGXXb0jooAiTJCageCGx5SeqNvdX7xwj0nBFMMrMpw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Thu, 22 Jul 2021 at 18:03, Mukesh Rajpurohit <vivasvan1902(at)gmail(dot)com>
wrote:
> Hi All,
> I have one procedure which has 4 input parameter as
> refcursor and they are as INOUT parameter.
>
> Now, I want to call that procedure in begin end; block and want to display
> those 4 refcursor result in pgadmin. I tried multiple option like unnamed
> portal, declare a refcursor and use in fetch all from..into...etc. But, no
> option is showing result.
>
> Please help if there is some solution for this, I tried all option
> available in sites but it did not worked.
>
I am not sure if i understood it correctly. you have a refcursor as param
in a procedure where you initialize and return a refcursor.
postgres=# create procedure p1 (INOUT r1 refcursor) as $$
begin
open r1 for select id from dates;
return;
end; $$ language plpgsql;
CREATE PROCEDURE
-- you would need a do block to declare vars as this would be plpgsql
postgres=# do $$
declare r1 refcursor; x record;
begin
call p1(r1);
for i in 1..10 loop
fetch next from r1 into x;
raise notice '%', x;
end loop;
end; $$ language plpgsql;
NOTICE: (1)
NOTICE: (2)
NOTICE: (3)
NOTICE: (4)
NOTICE: (5)
NOTICE: (6)
NOTICE: (7)
NOTICE: (8)
NOTICE: (9)
NOTICE: (10)
DO
something like this?
postgres/plpgsql.sql at master · postgres/postgres (github.com)
<https://github.com/postgres/postgres/blob/master/src/test/regress/sql/plpgsql.sql#L1868>
--
Thanks,
Vijay
Mumbai, India
From | Date | Subject | |
---|---|---|---|
Next Message | Thorsten Schöning | 2021-07-23 09:56:38 | What's the dfifference between pg_start_backup+copy+pg_stop_backup+WAL vs. pg_start_backup+pg_stop_backup+copy+WAL? |
Previous Message | Mukesh Rajpurohit | 2021-07-22 12:33:12 | Multiple refcursor as INOUT parameter in procedure |