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.kla...@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.kla...@aklaver.com > > <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > >
