Обсуждение: Stored Procedure / function and their result
Hi,
I would like to know if there is a better way how to retrieve result from a stored procedure (function) than to use 'AS res(col1 varchar, col2 timestamp,..)'
for example, here is a stored procedure :
CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
myrec RECORD;
BEGIN
FOR myrec IN
select
users.user_name,
users.user_firstname,
accounts.account_login,
statususer.statususer_type
from accounts, users, statususer
where
accounts.account_login = $1
AND
accounts.account_id = users.user_account_id
AND
users.user_status_id = statususer.statususer_id
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
...
here is how i call it :
select * from sp_a_003('my_user_name')
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar
);
to understand well, in my stored procedure i only select a part of each table (so i build a "composite" record) therefore i understood that SETOF RECORD AS was the best solution for that.
however the result call is catastrophic when stored procedure returns several fields. when it is more than 2 fields i'm already "angry" to write :
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar,
....
);
I would like to avoid this "as result (...)", so is there a better solution ?
thanks a lot,
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
I would like to know if there is a better way how to retrieve result from a stored procedure (function) than to use 'AS res(col1 varchar, col2 timestamp,..)'
for example, here is a stored procedure :
CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
myrec RECORD;
BEGIN
FOR myrec IN
select
users.user_name,
users.user_firstname,
accounts.account_login,
statususer.statususer_type
from accounts, users, statususer
where
accounts.account_login = $1
AND
accounts.account_id = users.user_account_id
AND
users.user_status_id = statususer.statususer_id
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
...
here is how i call it :
select * from sp_a_003('my_user_name')
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar
);
to understand well, in my stored procedure i only select a part of each table (so i build a "composite" record) therefore i understood that SETOF RECORD AS was the best solution for that.
however the result call is catastrophic when stored procedure returns several fields. when it is more than 2 fields i'm already "angry" to write :
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar,
....
);
I would like to avoid this "as result (...)", so is there a better solution ?
thanks a lot,
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
On Mon, Mar 19, 2007 at 01:54:14PM +0100, Alain Roger wrote: > Hi, > > I would like to know if there is a better way how to retrieve result from a > stored procedure (function) than to use 'AS res(col1 varchar, col2 > timestamp,..)' Sure, create a type with the relevent field name and use that in you function declaration: CREATE TYPE mytype AS S res(col1 varchar, col2 timestamp,..); > for example, here is a stored procedure : > CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR) > RETURNS SETOF RECORD AS And change that to: RETURNS SETOF mytype AS And you're done. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
Alain Roger wrote: > I would like to know if there is a better way how to retrieve > result from a stored procedure (function) than to use 'AS > res(col1 varchar, col2 timestamp,..)' > > for example, here is a stored procedure : > CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR) > RETURNS SETOF RECORD AS [...] Yes, there are two ways to avoid this. 1.) define a composite type: CREATE TYPE sp_a_result ( name varchar, firstname varchar, userlogin varchar, statustype varchar ); CREATE OR REPLACE FUNCTION SP_A_003 (username VARCHAR) RETURNS SETOF sp_a_result AS ... 2.) Use output parameters (for Versions >= 8.1): CREATE OR REPLACE FUNCTION SP_A_003 ( username IN VARCHAR, name OUT varchar, firstname OUT varchar, userlogin OUT varchar, statustype OUT varchar ) RETURNS SETOF record AS ... You can find a more verbose description in http://www.postgresql.org/docs/current/static/xfunc-sql.html Yours, Laurenz Albe