Thread: plpgsql

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

dim <dim45@gmx.net> writes:
> 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" ?

Might not be the only solution, but IMHO it's the correct solution.
Your underlying SELECT returns either a row, or no row (or possibly
more than one row?) so the function should too.

> Am I missing anything or is
> record assignment not possible in plpgsql?

Your example works fine for me in PG 8.0.  There were some improvements
in this area since 7.*, though I'm not sure offhand whether they were
material to this particular case.

            regards, tom lane

>>Am I missing anything or is
>>record assignment not possible in plpgsql?
>
>
> Your example works fine for me in PG 8.0.  There were some improvements
> in this area since 7.*, though I'm not sure offhand whether they were
> material to this particular case.
strange.
laik is the table with rows nr integer and txt varchar.

CREATE OR REPLACE FUNCTION tst1(integer) RETURNS laik AS '
  SELECT * FROM laik where nr=$1;
' LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION tst2(integer) RETURNS varchar AS '
DECLARE
    v laik;
BEGIN
    v := tst1($1);
    RETURN 1;
END;
' LANGUAGE 'plpgsql' VOLATILE;

if i try to 'select tst2(1);'
I get the following error message:
ERROR: syntax error at or near "v"
CONTEXT: compile of PL/pgSQL function "tst2" near line 4

I'm using Postgresql 7.4.3. Will try this with 8, but even if it works
with 8, it's not good - I don't know on what version of postgre my db
will later reside.

Thanks,
dim