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 ...
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.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
