Thread

1. Concat error in PL/pgsql

   Hans Peter Wuermli <wurmli@freesurf.ch> — 2001-01-06T17:29:22Z

   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 the following. 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();
   
   
   

2. Re: Concat error in PL/pgsql

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-06T21:38:21Z

   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
   
   

3. RE: Concat error in PL/pgsql

   Andrew Snow <andrew@modulus.org> — 2001-01-07T00:57:55Z

   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
   
   
   
   

4. RE: Concat error in PL/pgsql

   Andrew Snow <andrew@modulus.org> — 2001-01-07T08:51:51Z

   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;