From: | Hector Menchaca <hm34306(at)hotmail(dot)com> |
---|---|
To: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Function with OUT parameter and Return Query |
Date: | 2014-10-05 18:10:36 |
Message-ID: | BAY178-W41F053CCCAAAFD83C308A1F8A40@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Correct... in this case that wold suffice...
thanks
Date: Sun, 5 Oct 2014 10:06:04 +0200
Subject: Re: [SQL] Function with OUT parameter and Return Query
From: guillaume(at)lelarge(dot)info
To: hm34306(at)hotmail(dot)com
CC: pgsql-sql(at)postgresql(dot)org
Hi,
2014-10-05 6:23 GMT+02:00 Hector Menchaca <hm34306(at)hotmail(dot)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 AgentServerAS $$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.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
From | Date | Subject | |
---|---|---|---|
Next Message | jim_yates | 2014-10-08 18:00:13 | could not access status of transaction pg_multixact issue |
Previous Message | Guillaume Lelarge | 2014-10-05 08:06:04 | Re: Function with OUT parameter and Return Query |