Thread: plpgsql-fct. fails on NULL in record variables

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

concatenating a  NULL value to a string will return NULL.

SELECT NULL||'Stringggg';
 ?column?
----------

(1 row)

There's a fonction called coalesce that replaces NULL values with a
specified value it can be usefull:

SELECT coalesce(NULL,'') || ' Stringgggg';
  ?column?
-------------
  Stringgggg
(1 row)

SELECT coalesce('A','') || ' Stringgggg';
   ?column?
--------------
 A Stringgggg
(1 row)

HTH
Najib.




----- Original Message -----
From: "Daniel Martini" <dmartini@uni-hohenheim.de>
To: <pgsql-general@postgresql.org>
Sent: Friday, September 24, 2004 11:33 AM
Subject: [GENERAL] plpgsql-fct. fails on NULL in record variables


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match