[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Stored Procedure / function and their result
From:       "Alain Roger" <raf.news () gmail ! com>
Date:       2007-03-19 12:54:14
Message-ID: 75645bbb0703190554k5b62a790mc0cac0b57bf5e12e () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

Hi,<br><br>I would like to know if there is a better way how to retrieve result from \
a stored procedure (function) than to use &#39;AS res(col1 varchar, col2 \
timestamp,..)&#39;<br><br>for example, here is a stored procedure : <br>CREATE OR \
REPLACE FUNCTION SP_A_003(username VARCHAR)<br>&nbsp; RETURNS SETOF RECORD \
AS<br>$BODY$<br>DECLARE<br>&nbsp;&nbsp;&nbsp; myrec \
RECORD;<br>BEGIN<br>&nbsp;&nbsp;&nbsp; FOR myrec IN<br>&nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; select <br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; users.user_name,<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp;  users.user_firstname,<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; accounts.account_login,<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; statususer.statususer_type<br>&nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; from accounts, users, statususer<br>&nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; where<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; \
accounts.account_login = $1<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; AND  \
<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; accounts.account_id = \
users.user_account_id<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; AND \
<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; users.user_status_id = \
statususer.statususer_id<br>&nbsp;&nbsp;&nbsp; LOOP<br>&nbsp;&nbsp;&nbsp; \
&nbsp;&nbsp;&nbsp; RETURN NEXT myrec;<br>&nbsp;&nbsp;&nbsp; END \
LOOP;<br>RETURN;<br>END;<br>$BODY$ <br>&nbsp; LANGUAGE &#39;plpgsql&#39; \
VOLATILE;<br>...<br><br>here is how i call it :<br><br><br>select * from \
sp_a_003(&#39;my_user_name&#39;) <br>as result<br>(<br>&nbsp;&nbsp;&nbsp; name \
varchar,<br>&nbsp;&nbsp;&nbsp; firstname varchar,<br>&nbsp;&nbsp;&nbsp; userlogin \
varchar, <br>&nbsp;&nbsp;&nbsp; statustype varchar<br>);<br><br>to understand well, \
in my stored procedure i only select a part of each table (so i build a \
&quot;composite&quot; record) therefore i understood that SETOF RECORD AS was the \
best solution for that. <br><br>however the result call is catastrophic when stored \
procedure returns several fields. when it is more than 2 fields i&#39;m already \
&quot;angry&quot; to write :<br>as result<br> (<br>
&nbsp;&nbsp;&nbsp; name varchar,<br>
&nbsp;&nbsp;&nbsp; firstname varchar,<br>
&nbsp;&nbsp;&nbsp; userlogin varchar,<br>
&nbsp;&nbsp;&nbsp; statustype varchar,<br>&nbsp;&nbsp;&nbsp; ....<br>
);<br><br>I would like to avoid this &quot;as result (...)&quot;, so is there a \
better solution ?<br><br>thanks a lot,<br><br><br><br clear="all"><br>-- \
<br>Alain<br>------------------------------------<br>Windows XP SP2 <br>PostgreSQL \
8.1.4<br>Apache 2.0.58<br>PHP 5



[prev in list] [next in list] [prev in thread] [next in thread]