PL/pgSQL question

From: "Sebastian Ritter" <ritter(dot)sebastian(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: PL/pgSQL question
Date: 2008-02-14 13:23:33
Message-ID: 99b656cb0802140523o165f69ebtcb4a42307c085f18@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Milen A. Radev 2008-02-14 13:50:13 Re: PL/pgSQL question
Previous Message Bart Degryse 2008-02-13 17:16:11 Re: return field from different table conditionally