Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group