Обсуждение: PL/pgSQL question

Поиск
Список
Период
Сортировка

PL/pgSQL question

От
"Sebastian Ritter"
Дата:
Hi all,<br /><br />I have a question regarding functions.  How can I return zero rows from a function whose return type
isa table row?  I did the following test and it did not work as expected:<br /><br />CREATE OR REPLACE FUNCTION<br />
   fn_get_user (integer) RETURNS usertable AS '<br /><br />DECLARE<br />    in_userid              ALIAS for $1;<br
/>   resulter               usertable%ROWTYPE;<br /><br />BEGIN<br /><br />    IF in_userid IS NULL THEN<br />       
RAISEEXCEPTION ''No user provided'';<br />         RETURN null;<br />    END IF;<br /><br />    SELECT INTO resulter<br
/>               usertable.*<br />    FROM<br />        usertable<br />    WHERE<br />        id = in_userid;<br /><br
/>   IF FOUND THEN<br />        RETURN resulter;<br />     ELSE<br />        RETURN null;<br />    END IF;<br /><br
/>END;'LANGUAGEplpgsql;<br /><br />>select * from fn_get_user(-1);<br /> id | col1 | col2 | col3| name | email <br
/>----+------------+-------------+------------+--------<br/>     |            |             |            |       
|     |<br />(1 row)<br /><br />This returns a null row. I am trying to make it behave such that it returns zero rows
likea straight select.<br /><br />>select * from usertable where id  =-1;<br />  id | col1 | col2 | col3| name |
email<br /> ----+------------+-------------+------------+--------<br />(0 rows)<br /><br />Is this possible in
anyway?<br/><br />Regards,<br />Sebastian<br />

Re: PL/pgSQL question

От
"Milen A. Radev"
Дата:
Sebastian Ritter написа:
> Hi all,
> 
> I have a question regarding functions.  How can I return zero rows from a
> function whose return type is a table row?  I did the following test and it
> did not work as expected:
[...]


CREATE OR REPLACE FUNCTION foobar(boolean) RETURNS SETOF x AS $_$
DECLARE    res x%ROWTYPE;
BEGIN
    if $1 THEN        RETURN NEXT res;    ELSE        RETURN;    END IF;
END;$_$

LANGUAGE plpgsql;



-- 
Milen A. Radev