Re: Function with OUT parameter and Return Query - Mailing list pgsql-sql

From Guillaume Lelarge
Subject Re: Function with OUT parameter and Return Query
Date
Msg-id CAECtzeU-ytmcdYWXbZNHZ1Vv3O3+kxOB-cB1a0iH=-A-Co=F+w@mail.gmail.com
Whole thread Raw
In response to Function with OUT parameter and Return Query  (Hector Menchaca <hm34306@hotmail.com>)
Responses Re: Function with OUT parameter and Return Query
List pgsql-sql
Hi,

2014-10-05 6:23 GMT+02:00 Hector Menchaca <hm34306@hotmail.com>:
All,
Struggling 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, _port INTEGER, out _out_agent_server_id INTEGER)
RETURNS SETOF AgentServer
AS $$
BEGIN
Select _agent_server_id INTO _out_agent_server_id FROM sp_private_AgentServer_Insert(_agentserver_name, _port);

Update AgentServer
SET RegisteredOn = NOW()
where AgentServer_ID = _out_agent_server_id;

RETURN QUERY
Select * From AgentServer where AgentServer_ID = _out_agent_server_id;
END$$ LANGUAGE plpgsql;

In doing this an error is returned :
ERROR:  function result type must be integer because of OUT parameters

If I change to Integer, 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, you don't need the ", out _out_agent_server_id INTEGER" because you already have it in the AgentServer record it sends back. So get rid of it, and it should work.


--

pgsql-sql by date:

Previous
From: Hector Menchaca
Date:
Subject: Function with OUT parameter and Return Query
Next
From: Hector Menchaca
Date:
Subject: Re: Function with OUT parameter and Return Query