Re: Dynamic procedure execution

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

[1]
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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
> > <mailto: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
> > <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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>