Stored Procedure / function and their result

Started by Alain Rogerabout 19 years ago3 messagesgeneral
Jump to latest
#1Alain Roger
raf.news@gmail.com

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

#2Martijn van Oosterhout
kleptog@svana.org
In reply to: Alain Roger (#1)
Re: Stored Procedure / function and their result

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/

Show quoted text

From each according to his ability. To each according to his ability to litigate.

#3Albe Laurenz
all@adv.magwien.gv.at
In reply to: Alain Roger (#1)
Re: Stored Procedure / function and their result

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