471,090 Members | 1,371 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

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

Nov 23 '05 #1
1 2162
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" <dm******@uni-hohenheim.de>
To: <pg***********@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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Amin Schoeib | last post: by
6 posts views Thread by Martin Marques | last post: by
1 post views Thread by Rajesh Kumar Mallah | last post: by
reply views Thread by Steve Wampler | last post: by
10 posts views Thread by lnd | last post: by
2 posts views Thread by Mark Cave-Ayland | last post: by
reply views Thread by sripathy sena | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.