[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Dynamic procedure execution
From: Adrian Klaver <adrian.klaver () aklaver ! com>
Date: 2020-12-29 19:32:00
Message-ID: 5d2a6b05-5108-0af4-80da-4eb503a2b828 () aklaver ! com
[Download RAW message or body]
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic