Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 11F632E00FE
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 09:23:47 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 54128-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 14 Feb 2008 09:23:28 -0400 (AST)
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.226])
	by postgresql.org (Postfix) with ESMTP id AD2F02E004B
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 09:23:35 -0400 (AST)
Received: by wx-out-0506.google.com with SMTP id r21so307898wxc.28
	for <pgsql-sql@postgresql.org>; Thu, 14 Feb 2008 05:23:34 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type;
	bh=npBPq3KzfWZrMJ671aP2DmQnouWxApd1uL1s2kS8D5Q=;
	b=WVGKvgfStOY2NdOGNpbVCVVZ0tIXwMUHuAmRgQ+QE/JWefXSnUQaPj9A3uJfL7oBvuZpOsyKEV20favtaYdgFf0GiLrgvQLl0y+rmjR7AdIEk2EOE2JLHBB6aKiO4/xVD9zrhe7qYw4b4h0W/39Ytqc3yReXRCuNuWAHxnVS7QU=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type;
	b=wHJqd3KGuUt2bGWdRZXPjzZCaCzU49IyNY9qL/FxUSG95jR5lvxwM4kj9jQkwymVmJHeYuRLOgjIch07H/5Eh6e7bigBSvEJ/NDc7qQxwBHaPkqN9y1jYiN7yHoTCnW20AkjQ6z6HO4eGYTXxquS71GSuUDL9CfxSXgmg1YeSP4=
Received: by 10.141.171.6 with SMTP id y6mr950388rvo.143.1202995413729;
	Thu, 14 Feb 2008 05:23:33 -0800 (PST)
Received: by 10.141.185.9 with HTTP; Thu, 14 Feb 2008 05:23:33 -0800 (PST)
Message-ID: <99b656cb0802140523o165f69ebtcb4a42307c085f18@mail.gmail.com>
Date: Thu, 14 Feb 2008 13:23:33 +0000
From: "Sebastian Ritter" <ritter.sebastian@gmail.com>
To: pgsql-sql@postgresql.org
Subject: PL/pgSQL question
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1638_29668846.1202995413727"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/105
X-Sequence-Number: 30196

------=_Part_1638_29668846.1202995413727
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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
    fn_get_user (integer) RETURNS usertable AS '

DECLARE
    in_userid              ALIAS for $1;
    resulter               usertable%ROWTYPE;

BEGIN

    IF in_userid IS NULL THEN
        RAISE EXCEPTION ''No user provided'';
        RETURN null;
    END IF;

    SELECT INTO resulter
                usertable.*
    FROM
        usertable
    WHERE
        id = in_userid;

    IF FOUND THEN
        RETURN resulter;
    ELSE
        RETURN null;
    END IF;

END;'LANGUAGE plpgsql;

>select * from fn_get_user(-1);
 id | col1 | col2 | col3| name | email
----+------------+-------------+------------+--------
    |            |             |            |        |      |
(1 row)

This returns a null row. I am trying to make it behave such that it returns
zero rows like a straight select.

>select * from usertable where id  =-1;
 id | col1 | col2 | col3| name | email
----+------------+-------------+------------+--------
(0 rows)

Is this possible in anyway?

Regards,
Sebastian

------=_Part_1638_29668846.1202995413727
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_1638_29668846.1202995413727--