Re: Dynamic procedure execution

On 12/13/20 9:59 PM, Muthukumar.GK wrote:
> Hi team,
>
> When I am trying to implement belwo dynamic concept in postgreSql,
> getting some error. Kindly find the below attached program and error.
> Please advise me what is wrong here..
>
> CREATE OR REPLACE PROCEDURE DynamicProc()
>
> AS $$
>
> DECLARE v_query TEXT;
>
> C1 refcursor := 'result1';
>
> begin
>
> v_query := '';
>
> v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';
>
> EXECUTE (v_query);
>
> END;
>
> $$
>
> Language plpgsql;
>
>  Calling procedure :-
>
> --------------------------------
>
> CALL DynamicProc();
>
> FETCH ALL IN "result1";
>
>
> Error :-
>
> --------------
>
> ERROR: syntax error at or near "OPEN"
>
> LINE 1: OPEN C1 FOR SELECT * FROM public."Los_BankInfo" ^
>
> QUERY: OPEN C1 FOR SELECT * FROM public."Los_BankInfo"
>
> CONTEXT: PL/pgSQL function dynamicproc() line 9 at EXECUTE SQL state: 42601

Two things:

1) The error is from a different version of the procedure then the code.
The table name is different. Can't be sure that this is the only change.
So can you synchronize your code with the error.

2) Take a look here:

https://www.postgresql.org/docs/12/plpgsql-cursors.html

42.7.2. Opening Cursors

For why OPEN is plpgsql specific and how to use it.

>
>
> Regards
>
> Muthukumar.gk
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com