Multiple result set to be returned in procedure/function
Hi team, is it possible to return Multiple results set from procedure/function on single execution. Please advise me on this. we are planning to migrate things from sqlserver to postgresql where my existing sql stored procs will return multiple result set. so we need achieve same thing in postgresql. regards muthu 9894438403
Multiple result set not working
Hi team, is it possible to return Multiple results set from procedure/function on single execution. Please advise me on this. I have written sample code below and when i execute it i dont get any result set CREATE OR REPLACE FUNCTION multiResultset( ref1 refcursor, ref2 refcursor) RETURNS SETOF refcursor AS $$ BEGIN open ref1 for select * from los_approverreassignwhere pk_id= 10; open ref2 for select * from los_approverreassignwhere pk_id= 11; END; $$ LANGUAGE 'plpgsql'; [image: image.png] Regards Muthu
Multiple result set not displayed in PgAdmin4
Hi Team, I have created function in postgre sql 13 which will return two result. But when execute i can able to see the last cursor (query result set of last select )result set in Data output window of Pgadmin4. i tried executing the cursor inside the transaction as well (BEING.COMMIT). but i still do not see both result set one by one (likeSQL) in data output window. Please let me know if there is any limitation in pgadmin4 tool that multiple result set will not be displayed in Data output Window. Regards Muthukumar
Re: Multiple result set not displayed in PgAdmin4
Hi Adrian, If PG admin 4 return only last result set then, how can we call the procedure/function to get the multiple result set from dot net application. we are planning to migrate things from SQL to postgresql.. most of our existing SQL stored procedures will return multiple result sets. So we need to achieve Same multiple result set in postgreSql.. Kindly advise me.. Regards Muthukumar.GK On Mon, Nov 23, 2020, 8:49 PM Adrian Klaver wrote: > On 11/22/20 8:53 PM, Muthukumar.GK wrote: > > Hi Team, > > > > I have created function in postgre sql 13 which will return two result. > > But when execute i can able to see the last cursor (query result set of > > last select )result set in Data output window of Pgadmin4. i tried > > executing the cursor inside the transaction as well (BEING.COMMIT). > > but i still do not see both result set one by one (likeSQL) in data > > output window. Please let me know if there is any limitation in pgadmin4 > > tool that multiple result set will not be displayed in Data output > Window. > > From what I remember pgAdmin4 will only display the last result. > > > > > Regards > > Muthukumar > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Migration from SQL Server to PostgeSQL
HiTeam, We need to migrate tables,Records,Procedure and function from SQL server to Postgresql. Kindly let me know, Is there any tool or easiest way available to migrate from SQL Server to Postgresql. Please let me know if there are any limitations in *postgresql *compared to SQL server. Regards Muthu.
Re: Migration from SQL Server to PostgeSQL
Hi Pavel, Kindly let me know the following functionality can be possible in postgresql. If possible,provide me with a sample program. 1. Call the procedure from/inside another procedure and insert the result set into Temp Table. 2. Creating Dynamic procedure in postgresql. because We have dynamic procedure in SQL server, On execution of this Procedure, another two procedures will be Created dynamically. Regards Muthu On Mon, Nov 30, 2020 at 11:02 AM Pavel Stehule wrote: > > > po 30. 11. 2020 v 6:12 odesílatel Muthukumar.GK > napsal: > >> HiTeam, >> >> We need to migrate tables,Records,Procedure and function from SQL server >> to Postgresql. Kindly let me know, Is there any tool or easiest way >> available to migrate from SQL Server to Postgresql. Please let me know if >> there are any limitations in *postgresql *compared to SQL server. >> > > The migration from MSSQL can be difficult, and needs a lot of manual work. > The concept of procedures is very different. In MSSQL is natural returning > multirecordset from procedures. This is not possible in Postgres, where > design of procedures (or all design) is similar to Oracle. It is possible. > I know some customers that did successful migrations from Sybase to > Postgres (Sybase had the same roots like MSSQL). > > Regards > > Pavel > > >> Regards >> Muthu. >> >
Calling Procedure from another procedure in Postgres
Hi team, I need to call the procedure(not function) and insert the records into a temporary table from another procedure in postgres. When executing the procedure 'Sampleproc2',I got some below syntax error. Kindly let me know whether postgres supports this functionality or any other way of calling the procedure from another procedure. CREATE OR REPLACE PROCEDURE SampleProc1() as$$ declare c1 refcursor:='result1'; begin open c1 for select approverid,assigntoid,effstdt,effenddtfrom tblApproverreassign; end; $$ language plpgsql; - CREATE OR REPLACE PROCEDURE SampleProc2() as $$ declare c1 refcursor:='result1'; begin CREATE TEMP TABLE TMPApproverAssign ( approverid VARCHAR(10), assigntoid VARCHAR(10), effstdt timestamptz, effenddttimestamptz ) ON COMMIT DROP; INSERT INTO TMPApproverAssign *CALL SampleProc1(); * open c1 for select approverid,assigntoid,effstdt,effenddtfrom TMPApproverAssign; end; $$ language plpgsql; - Error : syntax error at or near "CALL" LINE 12 : ^CALL SampleProc1(); SQL state : 42601 Character:453 --
Accessing Postgres Server and database from other Machine
Hi Team, I have created Database in postgresqlv13 . My Office colleague needs to access my postgres server and database from his machine. There is a config file called 'Postgresql.conf.sample' in program files folder. Even if I changed the connection settings of listen address from localhost to *( Connection Settings - #*Listen address* =*'*' *) in config file. But still my colleague is not able to connect it. Kindly provide some guidance on this. Regards Muthu
Re: Accessing Postgres Server and database from other Machine
No.I did not restart postgres.Please let me know the steps to restart postgres using pgadmin4 tool. On Fri, Dec 4, 2020 at 5:26 PM Hemil Ruparel wrote: > Are you sure you restarted postgres after the change in configuration? > > On Fri, Dec 4, 2020 at 3:58 PM Muthukumar.GK > wrote: > >> Hi Team, >> >> I have created Database in postgresqlv13 . My Office colleague needs to >> access my postgres server and database from his machine. There is a config >> file called 'Postgresql.conf.sample' in program files folder. Even if I >> changed the connection settings of listen address from localhost to *( >> Connection Settings - #*Listen address* =*'*' *) in config file. But >> still my colleague is not able to connect it. Kindly provide some guidance >> on this. >> >> Regards >> Muthu >> >
Re: Accessing Postgres Server and database from other Machine
Hi Nicklas, I have added a line "host all all 0.0.0.0/0 md5" in Pg_hba_conf.sample file and restarted postgres server. porstgres port has been added in windows firewall (Windows10)as well. But my colleague is still facing the below error when connecting my postgres server from .net appplication. Error is - no connection could be made because the target machine actively refused it. Regards Muthu On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén wrote: > > > On 4 December 2020 17:17:48 CET, Adrian Klaver > wrote: > >On 12/4/20 8:03 AM, Paul Förster wrote: > >> Hi Adrian, > >> > >>> On 04. Dec, 2020, at 16:13, Adrian Klaver > > wrote: > >>> That is the wrong file, the *.sample is the giveaway. > >> > >> hmmm, I'd rather call it essential reference documentation or > >template for automation. It's perfectly well suited to automatically > >strip all comments and then diff the result to ones real world > >postgresql.conf or some other version postgresql.conf file to find > >parameters that have been removed or changed with a new PostgreSQL > >version. This is highly useful for planning migrations and have a quick > >reference what to check for before actually migrating. So for me this > >is much more than just a giveaway. > >> > > > >Yes, but for changing the behavior of a running instance it is the > >wrong > >file and it's extension is a clue. > > > You will prabably also need to change in tge pg_hba.conf file. At least in > linux there is no entry for connections from the outside there. You will > need a "host" entry accepting any ip address or specify what ip your > collegue is connecting from if possible. I have missed this step a few > times and banged my head. > > /Nicklas > On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén wrote: > > > On 4 December 2020 17:17:48 CET, Adrian Klaver > wrote: > >On 12/4/20 8:03 AM, Paul Förster wrote: > >> Hi Adrian, > >> > >>> On 04. Dec, 2020, at 16:13, Adrian Klaver > > wrote: > >>> That is the wrong file, the *.sample is the giveaway. > >> > >> hmmm, I'd rather call it essential reference documentation or > >template for automation. It's perfectly well suited to automatically > >strip all comments and then diff the result to ones real world > >postgresql.conf or some other version postgresql.conf file to find > >parameters that have been removed or changed with a new PostgreSQL > >version. This is highly useful for planning migrations and have a quick > >reference what to check for before actually migrating. So for me this > >is much more than just a giveaway. > >> > > > >Yes, but for changing the behavior of a running instance it is the > >wrong > >file and it's extension is a clue. > > > You will prabably also need to change in tge pg_hba.conf file. At least in > linux there is no entry for connections from the outside there. You will > need a "host" entry accepting any ip address or specify what ip your > collegue is connecting from if possible. I have missed this step a few > times and banged my head. > > /Nicklas >
Re: Accessing Postgres Server and database from other Machine
Yes. I restarted (Run->Services.msc->Postgresql13->Restart) after config
file.
I can able to connect my postgres server using .net application with help
of below connection string.
NpgsqlConnectionconn =newNpgsqlConnection("Server=127.0.0.1;User Id=postgres;
"+
"Password=Postgresql@1;Database=Training;");
but my colleague is facing restriction issue when trying to connect my
postgres server using *my Ip Address*,
NpgsqlConnectionconn =newNpgsqlConnection("Server=192.1xx.xx.50;User Id=
postgres; "+
"Password=Postgresql@1;Database=Training;");
Regards
Muthu
On Sat, Dec 5, 2020 at 12:21 PM Hemil Ruparel
wrote:
> Did you restart postgres after changing pg_hba.conf?
>
> On Sat, Dec 5, 2020 at 11:56 AM Muthukumar.GK
> wrote:
>
>> Hi Nicklas,
>>
>> I have added a line "host all all 0.0.0.0/0 md5" in
>> Pg_hba_conf.sample file and restarted postgres server. porstgres port has
>> been added in windows firewall (Windows10)as well. But my colleague is
>> still facing the below error when connecting my postgres server from .net
>> appplication.
>>
>> Error is - no connection could be made because the target machine
>> actively refused it.
>>
>> Regards
>> Muthu
>>
>> On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén
>> wrote:
>>
>>>
>>>
>>> On 4 December 2020 17:17:48 CET, Adrian Klaver <
>>> adrian.kla...@aklaver.com> wrote:
>>> >On 12/4/20 8:03 AM, Paul Förster wrote:
>>> >> Hi Adrian,
>>> >>
>>> >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
>>> > wrote:
>>> >>> That is the wrong file, the *.sample is the giveaway.
>>> >>
>>> >> hmmm, I'd rather call it essential reference documentation or
>>> >template for automation. It's perfectly well suited to automatically
>>> >strip all comments and then diff the result to ones real world
>>> >postgresql.conf or some other version postgresql.conf file to find
>>> >parameters that have been removed or changed with a new PostgreSQL
>>> >version. This is highly useful for planning migrations and have a quick
>>> >reference what to check for before actually migrating. So for me this
>>> >is much more than just a giveaway.
>>> >>
>>> >
>>> >Yes, but for changing the behavior of a running instance it is the
>>> >wrong
>>> >file and it's extension is a clue.
>>>
>>>
>>> You will prabably also need to change in tge pg_hba.conf file. At least
>>> in linux there is no entry for connections from the outside there. You will
>>> need a "host" entry accepting any ip address or specify what ip your
>>> collegue is connecting from if possible. I have missed this step a few
>>> times and banged my head.
>>>
>>> /Nicklas
>>>
>>
>> On Sat, Dec 5, 2020 at 1:36 AM Nicklas Avén
>> wrote:
>>
>>>
>>>
>>> On 4 December 2020 17:17:48 CET, Adrian Klaver <
>>> adrian.kla...@aklaver.com> wrote:
>>> >On 12/4/20 8:03 AM, Paul Förster wrote:
>>> >> Hi Adrian,
>>> >>
>>> >>> On 04. Dec, 2020, at 16:13, Adrian Klaver
>>> > wrote:
>>> >>> That is the wrong file, the *.sample is the giveaway.
>>> >>
>>> >> hmmm, I'd rather call it essential reference documentation or
>>> >template for automation. It's perfectly well suited to automatically
>>> >strip all comments and then diff the result to ones real world
>>> >postgresql.conf or some other version postgresql.conf file to find
>>> >parameters that have been removed or changed with a new PostgreSQL
>>> >version. This is highly useful for planning migrations and have a quick
>>> >reference what to check for before actually migrating. So for me this
>>> >is much more than just a giveaway.
>>> >>
>>> >
>>> >Yes, but for changing the behavior of a running instance it is the
>>> >wrong
>>> >file and it's extension is a clue.
>>>
>>>
>>> You will prabably also need to change in tge pg_hba.conf file. At least
>>> in linux there is no entry for connections from the outside there. You will
>>> need a "host" entry accepting any ip address or specify what ip your
>>> collegue is connecting from if possible. I have missed this step a few
>>> times and banged my head.
>>>
>>> /Nicklas
>>>
>>
Dynamic procedure execution
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 Regards Muthukumar.gk
Re: Dynamic procedure execution
Hi David, As I am not bit Clea, let me know what I have to do. If possible, please re- write my program. Regards Muthu On Mon, Dec 14, 2020, 11:43 AM David G. Johnston wrote: > On Sunday, December 13, 2020, Muthukumar.GK wrote: > >> >> v_query := ' OPEN C1 FOR SELECT * FROM public."Bankdetails"'; >> >> >> >> EXECUTE (v_query); >> >> >> > You put the pl/pgsql OPEN command into a string and sent it to the SQL > engine via EXECUTE and the SQL engine is complaining that it has no idea > what you want it to do. > > David J. > >
Re: Dynamic procedure execution
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. *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 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 > > 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 >
