Re: Dynamic procedure execution

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