Stored Procedure / function and their result

["Alain Roger" <raf.news@xxxxxxxxx>]

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