From: | "Alain Roger" <raf(dot)news(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Stored Procedure / function and their result |
Date: | 2007-03-19 12:54:14 |
Message-ID: | 75645bbb0703190554k5b62a790mc0cac0b57bf5e12e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2007-03-19 13:02:04 | Re: Stored Procedure / function and their result |
Previous Message | Ben Trewern | 2007-03-19 12:09:48 | Re: UPGRADATION TO 8.1 |