plpgsql-fct. fails on NULL in record variables

From: Daniel Martini <dmartini(at)uni-hohenheim(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql-fct. fails on NULL in record variables
Date: 2004-09-24 09:33:00
Message-ID: 1096018380.4153e9cc855f9@webmail.uni-hohenheim.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'm currently coding some functions in plpgsql for generating
reports out of records in a table. Problem is: NULL values in
records make the complete function fail.
Here is a simple test case (original is more complex with a multi-
table query in the for qres in select... part):

create table test(
id serial,
descr char(4),
data int
);

insert into test (descr, data) values ('set1', 15);
-- record 2 does not have a data value --
insert into test (descr) values ('set2');

create function report(int) returns text as '
declare
qres record;
report text;
begin
for qres in
select descr, data from test where id=$1
loop
report:=qres.descr||'': ''||qres.data;
end loop;
return report;
end;'
language 'plpgsql';

now test the function in psql:
test=> select report(1);
report
----------
set1: 15
(1 row)

test=> select report(2);
report
--------

(1 row)

while what I want it to return in the second case is this:
report
--------
set2:
(1 row)

'set2: NULL' would be ok as well.

How can I achieve this?

Regards,
Daniel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mike 2004-09-24 10:58:44 formating interval question
Previous Message noreply 2004-09-24 09:15:41 Is linked servers possible with postgresql?