Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XaqGn-00032U-FJ for pgsql-sql@arkaria.postgresql.org; Sun, 05 Oct 2014 18:10:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XaqGm-0006ZJ-5N for pgsql-sql@arkaria.postgresql.org; Sun, 05 Oct 2014 18:10:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XaqGj-0006ZA-Sz for pgsql-sql@postgresql.org; Sun, 05 Oct 2014 18:10:53 +0000 Received: from bay004-omc1s7.hotmail.com ([65.54.190.18]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XaqGW-0006Jv-C4 for pgsql-sql@postgresql.org; Sun, 05 Oct 2014 18:10:49 +0000 Received: from BAY178-W41 ([65.54.190.59]) by BAY004-OMC1S7.hotmail.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.22751); Sun, 5 Oct 2014 11:10:36 -0700 X-TMN: [mRefCEmrLxfX1MbrTbpzmjJ4p2bMyHDjAENOF4DffzI=] X-Originating-Email: [hm34306@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_7e9d3a4d-a30b-4166-b5f7-e0fe57775e32_" From: Hector Menchaca To: Guillaume Lelarge CC: "pgsql-sql@postgresql.org" Subject: Re: Function with OUT parameter and Return Query Date: Sun, 5 Oct 2014 13:10:36 -0500 Importance: Normal In-Reply-To: References: , MIME-Version: 1.0 X-OriginalArrivalTime: 05 Oct 2014 18:10:36.0732 (UTC) FILETIME=[A9B467C0:01CFE0C7] X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_7e9d3a4d-a30b-4166-b5f7-e0fe57775e32_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Correct... in this case that wold suffice... thanks Date: Sun=2C 5 Oct 2014 10:06:04 +0200 Subject: Re: [SQL] Function with OUT parameter and Return Query From: guillaume@lelarge.info To: hm34306@hotmail.com CC: pgsql-sql@postgresql.org Hi=2C 2014-10-05 6:23 GMT+02:00 Hector Menchaca : =0A= =0A= =0A= All=2CStruggling tying to get a function that works in Maraidb stored procs= ...looking to return an OUT Parameter value with Return Query CREATE FUNCTION sp_AgentServer_Register (_agentserver_name TEXT=2C _port IN= TEGER=2C out _out_agent_server_id INTEGER) RETURNS SETOF AgentServerAS $$BE= GIN Select _agent_server_id INTO _out_agent_server_id FROM sp_private_Agen= tServer_Insert(_agentserver_name=2C _port)=3B Update AgentServer SET RegisteredOn =3D NOW() where AgentServer_ID =3D _o= ut_agent_server_id=3B RETURN QUERY Select * From AgentServer where AgentServer_ID =3D _out_agent= _server_id=3B END$$ LANGUAGE plpgsql=3B In doing this an error is returned :ERROR: function result type must be in= teger because of OUT parameters If I change to Integer=2C then I get an Error From the return query...ERROR= : cannot use RETURN QUERY in a non-SETOF function Is there a way to do this? (I'm assuming no at this point... i hoping there= is some flag or something that I can set...)I can do this with MariaDB and= SqlServer... In the above function=2C you don't need the "=2C out _out_agent_server_id I= NTEGER" because you already have it in the AgentServer record it sends back= . So get rid of it=2C and it should work. --=20 Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com =0A= = --_7e9d3a4d-a30b-4166-b5f7-e0fe57775e32_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Correct... in this case that wol= d suffice...

thanks

= Date: Sun=2C 5 Oct 2014 10:06:04 +0200
Subject: Re: [SQL] Function with = OUT parameter and Return Query
From: guillaume@lelarge.info
To: hm343= 06@hotmail.com
CC: pgsql-sql@postgresql.org

Hi= =2C

2014-10-05 6:23 GMT+02:00 Hector Menchaca <=3Bhm34306@hotmail.com&g= t=3B:
=0A= =0A= =0A=
All=2C
Struggling tying to get a function that wo= rks in Maraidb stored procs...
looking to return an OUT Parameter= value with Return Query

CREATE FUNCTION sp_AgentServer_Register (_agentserver_name TEXT=2C= _port INTEGER=2C out _out_agent_server_id INTEGER)
<= span style=3D"white-space:pre-wrap=3B"> RETURNS SETOF =3BAgentServer
AS $$
BEG= IN
Select _agent_server_id INTO _= out_agent_server_id FROM sp_private_AgentServer_Insert(_agentserver_name=2C= _port)=3B

Update AgentServer
SET RegisteredOn =3D NOW()
where AgentServer_ID =3D _out_agent_server_id=3B

RETU= RN QUERY
Select *= From AgentServer where AgentServer_ID =3D _out_agent_server_id=3B
END$$ LANGUAGE= plpgsql=3B

In doing this an error is return= ed :
ERROR:  =3Bfunction result type must be integer because = of OUT parameters

If I change to Integer=2C then I= get an Error From the return query...
ERROR: cannot use RETURN Q= UERY in a non-SETOF function

Is there a way to do = this? (I'm assuming no at this point... i hoping there is some flag or some= thing that I can set...)
I can do this with MariaDB and SqlServer= ...


In the above function=2C you don't need the "=2C out _out_agent_server_id INTEGER" because you already have it= in the AgentServer record it sends back. So get rid of it=2C and it should= work.


--
<= div dir=3D"ltr">
Guillaume.
 =3B http://www.dalibo.com
=0A=
= --_7e9d3a4d-a30b-4166-b5f7-e0fe57775e32_--