Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XadMT-0005pC-Cs for pgsql-sql@arkaria.postgresql.org; Sun, 05 Oct 2014 04:23:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XadMR-0003oJ-JU for pgsql-sql@arkaria.postgresql.org; Sun, 05 Oct 2014 04:23:55 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XadMP-0003o9-DC for pgsql-sql@postgresql.org; Sun, 05 Oct 2014 04:23:53 +0000 Received: from bay004-omc2s26.hotmail.com ([65.54.190.101]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XadMK-0004Lz-PQ for pgsql-sql@postgresql.org; Sun, 05 Oct 2014 04:23:50 +0000 Received: from BAY178-W31 ([65.54.190.123]) by BAY004-OMC2S26.hotmail.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.22751); Sat, 4 Oct 2014 21:23:47 -0700 X-TMN: [HCT6csdQZzLufZN2OwLEh5rvS4D5YpHB] X-Originating-Email: [hm34306@hotmail.com] Message-ID: Content-Type: multipart/alternative; boundary="_a64deeee-272a-4884-94a3-a2de7c14a34d_" From: Hector Menchaca To: "pgsql-sql@postgresql.org" Subject: Function with OUT parameter and Return Query Date: Sat, 4 Oct 2014 23:23:47 -0500 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 05 Oct 2014 04:23:47.0542 (UTC) FILETIME=[2852BF60:01CFE054] X-Pg-Spam-Score: -1.9 (-) 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 --_a64deeee-272a-4884-94a3-a2de7c14a34d_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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... Any thoughts are appreciated. = --_a64deeee-272a-4884-94a3-a2de7c14a34d_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

All=2C

Struggling tying to g= et a function that works in Maraidb stored procs...

looking to re= turn an OUT Parameter value with Return Query

CREATE FUNCTION sp_AgentServer_Register (_ag= entserver_name TEXT=2C _port INTEGER=2C out _out_agent_server_id INTEGER)

RETURNS SETOF =3BAgentSer= ver

AS $$

BEGIN

Select _agent_server_id INTO _o= ut_agent_server_id FROM sp_private_AgentServer_Insert(_agentserver_name=2C = _port)=3B

Update AgentServer

SET RegisteredOn =3D NOW()

whe= re AgentServer_ID =3D _out_agent_server_id=3B

RETURN QUERY

S= elect * From AgentServer where AgentServer_ID =3D _out_agent_server_id=3B

<= /div>

END$$ LANGUAGE plpgsql=3B

In doing= this an error is returned :

ERROR: =3Bfunction result type = must be integer because of OUT parameters

If I cha= nge to Integer=2C then I get an Error From the return query...

ER= ROR: 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 th= ere is some flag or something that I can set...)

I can do this wi= th MariaDB and SqlServer...

Any thoughts are appre= ciated.

=

= --_a64deeee-272a-4884-94a3-a2de7c14a34d_--