Thread: Concat error in PL/pgsql
I don't have a bug template at hand. Within a plpgsql function, concatenating TEXT strings from tables that allow NULL values return nil results. Please run thefollowing. The result of the second select is nil. Cheers, H.P. ----------------------------------------------------------- create table tconcattest (id char(3),str text); insert into tconcattest values('hpw',text('Something...')); insert into tconcattest values('wuh',text('and more of something.')); create function fconcattest () returns text as' declare r record; output text; begin output := text(''''); for r in select * from tconcattest loop output := output || r.str; end loop; return output; end; ' language 'plpgsql'; select fconcattest(); insert into tconcattest values('abc',NULL); select fconcattest(); update tconcattest set str='...again' where id='abc'; select fconcattest();
Hans Peter Wuermli <wurmli@freesurf.ch> writes: > Within a plpgsql function, concatenating TEXT strings from tables that > allow NULL values return nil results. That's not a bug: NULL concatenated with anything produces NULL, per SQL92 specification. If you want a NULL to act like an empty string, try for r in select * from tconcattest loop output := output || coalesce(r.str, ''''); regards, tom lane
Simple add lines as shown: > for r in select * from tconcattest loop IF r.str IS NOT NULL THEN > output := output || r.str; END IF; > end loop; - Andrew
A few hours ago, I wrote: > > Simple add lines as shown: > > > for r in select * from tconcattest loop > IF r.str IS NOT NULL THEN > > output := output || r.str; > END IF; > > end loop; > This would probably be better: for r in select * from tconcattest where str is not null loop output := output || r.str; end loop;