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