Thread: Function with OUT parameter and Return Query
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...
Any thoughts are appreciated.
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 QueryCREATE FUNCTION sp_AgentServer_Register (_agentserver_name TEXT, _port INTEGER, out _out_agent_server_id INTEGER)RETURNS SETOF AgentServerAS $$BEGINSelect _agent_server_id INTO _out_agent_server_id FROM sp_private_AgentServer_Insert(_agentserver_name, _port);Update AgentServerSET RegisteredOn = NOW()where AgentServer_ID = _out_agent_server_id;RETURN QUERYSelect * 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 parametersIf I change to Integer, then I get an Error From the return query...ERROR: cannot use RETURN QUERY in a non-SETOF functionIs 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.
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@lelarge.info
To: hm34306@hotmail.com
CC: pgsql-sql@postgresql.org
Date: Sun, 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,
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 QueryCREATE FUNCTION sp_AgentServer_Register (_agentserver_name TEXT, _port INTEGER, out _out_agent_server_id INTEGER)RETURNS SETOF AgentServerAS $$BEGINSelect _agent_server_id INTO _out_agent_server_id FROM sp_private_AgentServer_Insert(_agentserver_name, _port);Update AgentServerSET RegisteredOn = NOW()where AgentServer_ID = _out_agent_server_id;RETURN QUERYSelect * 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 parametersIf I change to Integer, then I get an Error From the return query...ERROR: cannot use RETURN QUERY in a non-SETOF functionIs 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.