From: | dim <dim45(at)gmx(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | plpgsql |
Date: | 2005-02-12 08:49:48 |
Message-ID: | 420DC32C.5090901@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
I've got two questions about plpgsql. Couldn't find the answer in the
manual nor anywhere on the net.
Let's say I've got a table A with columns (id integer, txt varchar) and
a function get_a(integer):
CREATE OR REPLACE FUNCTION get_a(integer) RETURNS a as '
SELECT * FROM a WHERE id=$1;
' LANGUAGE 'sql';
First question: it is possible that a non-existant id will be requested,
i need the function to return the same thing a normal select would
return - an empty result set. However I get an error whenever the select
returns nothing. The error is "Function returning row cannot return null
value". Is the only solution to this problem to declare function get_a
to return "SET OF a" ?
Second question I've got a plpgsql function in which I need the result
of my function get_a.
CREATE OR REPLACE FUNCTION do_stuff(integer) RETURNS integer as '
DECLARE
var a;
...
BEGIN
var := get_a($1);
...
END;
' LANGUAGE 'plpgsql';
I get a syntex error on the line of the assignment (var := get_a($1)).
IF I replace the assignment with a "select into var * from get_a($1)"
then everything is ok. It also works with simple types (like varchar,
integer ant etc.), but not with row-types. Am I missing anything or is
record assignment not possible in plpgsql?
Thanks in advance,
dim
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-02-12 09:12:56 | Re: plpgsql |
Previous Message | Sal Dkj | 2005-02-12 00:38:23 | Re: Converting interval to numeric? |