Thread: Dynamic procedure execution

Dynamic procedure execution

From
"Muthukumar.GK"
Date:
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


Regards

Muthukumar.gk

Re: Dynamic procedure execution

From
"David G. Johnston"
Date:
On Sunday, December 13, 2020, Muthukumar.GK <muthankumar@gmail.com> wrote:

v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';

 

EXECUTE (v_query);



You put the pl/pgsql OPEN command into a string and sent it to the SQL engine via EXECUTE and the SQL engine is complaining that it has no idea what you want it to do.

David J.

Re: Dynamic procedure execution

From
"Muthukumar.GK"
Date:
Hi David,

As I am not bit Clea, let me know what I have to do. If possible, please re- write my program.

Regards
Muthu

On Mon, Dec 14, 2020, 11:43 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, December 13, 2020, Muthukumar.GK <muthankumar@gmail.com> wrote:

v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';

 

EXECUTE (v_query);



You put the pl/pgsql OPEN command into a string and sent it to the SQL engine via EXECUTE and the SQL engine is complaining that it has no idea what you want it to do.

David J.

Re: Dynamic procedure execution

From
Hemil Ruparel
Date:
Not to be disrespectful, but you need to at least struggle to find the answers yourself before posting here.

On Mon, Dec 14, 2020 at 12:02 PM Muthukumar.GK <muthankumar@gmail.com> wrote:
Hi David,

As I am not bit Clea, let me know what I have to do. If possible, please re- write my program.

Regards
Muthu

On Mon, Dec 14, 2020, 11:43 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, December 13, 2020, Muthukumar.GK <muthankumar@gmail.com> wrote:

v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"';

 

EXECUTE (v_query);



You put the pl/pgsql OPEN command into a string and sent it to the SQL engine via EXECUTE and the SQL engine is complaining that it has no idea what you want it to do.

David J.

Re: Dynamic procedure execution

From
"David G. Johnston"
Date:

On Sunday, December 13, 2020, Muthukumar.GK <muthankumar@gmail.com> wrote:
Hi David,

As I am not bit Clea, let me know what I have to do. If possible, please re- write my program.

Like the SQL executor, I have no idea what you are trying to do there.  Neither the text variable, nor the cursor, nor plpgsql for that matter, provide any benefit to executing “select from table”.

David J.

Re: Dynamic procedure execution

From
"David G. Johnston"
Date:
On Monday, December 14, 2020, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Sunday, December 13, 2020, Muthukumar.GK <muthankumar@gmail.com> wrote:
Hi David,

As I am not bit Clea, let me know what I have to do. If possible, please re- write my program.

Like the SQL executor, I have no idea what you are trying to do there.  Neither the text variable, nor the cursor, nor plpgsql for that matter, provide any benefit to executing “select from table”.


That said, kf you read the “see also” part of the FETCH documentation you will see that DECLARE is the SQL way to dpecify a cursor, not OPEN.

David J.
 

Re: Dynamic procedure execution

From
Adrian Klaver
Date:
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.klaver@aklaver.com



Re: Dynamic procedure execution

From
"Muthukumar.GK"
Date:

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.klaver@aklaver.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.klaver@aklaver.com

Re: Dynamic procedure execution

From
Adrian Klaver
Date:
On 12/28/20 10:34 PM, Muthukumar.GK wrote:

Pleas do not top post, the style on this list is bottom/inline posting.
> 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.

See here:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE

"
42.6.2. Returning from a Procedure

A procedure does not have a return value. A procedure can therefore end 
without a RETURN statement. If you wish to use a RETURN statement to 
exit the code early, write just RETURN with no expression.

If the procedure has output parameters, the final values of the output 
parameter variables will be returned to the caller.
"

So use a function and follow the docs here:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

in particular:

"42.6.1.2. RETURN NEXT and RETURN QUERY"

> 
> _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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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
>     <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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Dynamic procedure execution

From
Mark Johnson
Date:
Don't you have to select into a variable and then return the variable to the client per [1]?

Consider the following example from my Oracle system:

beginning code ...
  V_SQL := 'SELECT COUNT(*) FROM ' || V_TAB;
  EXECUTE IMMEDIATE V_SQL INTO V_CNT;
ending code ...



On Tue, Dec 29, 2020 at 11:40 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/28/20 10:34 PM, Muthukumar.GK wrote:

Pleas do not top post, the style on this list is bottom/inline posting.
> 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.

See here:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE

"
42.6.2. Returning from a Procedure

A procedure does not have a return value. A procedure can therefore end
without a RETURN statement. If you wish to use a RETURN statement to
exit the code early, write just RETURN with no expression.

If the procedure has output parameters, the final values of the output
parameter variables will be returned to the caller.
"

So use a function and follow the docs here:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

in particular:

"42.6.1.2. RETURN NEXT and RETURN QUERY"

>
> _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.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.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
>     <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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Dynamic procedure execution

From
Adrian Klaver
Date:
On 12/29/20 9:29 AM, Mark Johnson wrote:
> Don't you have to select into a variable and then return the variable to 
> the client per [1]?

Except PROCEDUREs do not return things(INOUT excepted), it would need to 
be a FUNCTION.

> 
> Consider the following example from my Oracle system:
> 
> beginning code ...
>    V_SQL := 'SELECT COUNT(*) FROM ' || V_TAB;
>    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
> ending code ...
> 
> [1] 
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN 
> <https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN>. 
> 
> 
> 
> On Tue, Dec 29, 2020 at 11:40 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 12/28/20 10:34 PM, Muthukumar.GK wrote:
> 
>     Pleas do not top post, the style on this list is bottom/inline posting.
>      > 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.
> 
>     See here:
> 
>     https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE
>     <https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE>
> 
>     "
>     42.6.2. Returning from a Procedure
> 
>     A procedure does not have a return value. A procedure can therefore end
>     without a RETURN statement. If you wish to use a RETURN statement to
>     exit the code early, write just RETURN with no expression.
> 
>     If the procedure has output parameters, the final values of the output
>     parameter variables will be returned to the caller.
>     "
> 
>     So use a function and follow the docs here:
> 
>     https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>     <https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING>
> 
>     in particular:
> 
>     "42.6.1.2. RETURN NEXT and RETURN QUERY"
> 
>      >
>      > _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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.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
>     <https://www.postgresql.org/docs/12/plpgsql-cursors.html>
>      >     <https://www.postgresql.org/docs/12/plpgsql-cursors.html
>     <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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com