Re: Dynamic procedure execution

Hi Adrian Klaver,

Sorry for typo mistake. Instead of writing lengthy query, I had written it
simple. Actually my main concept is to bring result set with multiple rows
(using select query) with help of dynamic query.

When calling that procedure in Pgadmin4 window, simply getting the message
as ‘ CALL Query returned successfully in 158 msec’.

FYI, I have implemented simple dynamic query for UPDATE and DELETE rows. It
is working fine without any issues.

Please let me know is there any way of getting result set using dynamic
query.

*Issue with dynamic select:-*

CREATE OR REPLACE
Procedure sp_select_dynamic_sql(

keyvalue integer)

LANGUAGE 'plpgsql'

AS $BODY$

Declare v_query text;

BEGIN

v_query:= 'select * from Los_BankInfo '

|| ' where pk_id = '

|| quote_literal(keyvalue);

execute v_query;

END;

$BODY$;

*Execuion** of Proc:-*

CALL sp_select_dynamic_sql (11);

*Output:-*

CALL

Query returned successfully in 158 msec.

*Working fine with Dynamic UPDATE and DELETE Statement :-*

*UPDATE:-*

CREATE OR REPLACE Procedure sp_Update_dynamic_sql(

newvalue varchar(10),

keyvalue integer)

LANGUAGE 'plpgsql'

AS $BODY$

Declare v_query text;

BEGIN

v_query:= 'update Los_BankInfo set approverid'

|| ' = '

|| quote_literal(newvalue)

|| ' where pk_id = '

|| quote_literal(keyvalue);

execute v_query;

END;

$BODY$;

--CALL sp_Update_dynamic_sql (john,10);

*DELETE:-*

CREATE OR REPLACE
Procedure sp_Delete_dynamic_sql(

keyvalue integer)

LANGUAGE 'plpgsql'

AS $BODY$

Declare v_query text;

BEGIN

v_query:= 'delete from Los_BankInfo '

|| ' where pk_id = '

|| quote_literal(keyvalue);

execute v_query;

END;

$BODY$;

--CALL sp_Delete_dynamic_sql(10);

Regards

Muthu

On Mon, Dec 14, 2020, 8:54 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>