Thread

1. maybe a bug in plpgsql, nulls and empty strings are not the same

   Domingo Alvarez Duarte <domingo@dad-it.com> — 2001-05-27T17:00:37Z

   When trying write a function in plpgsql I'm getting behavior that
   probably isn't the corect one.
   
   in the function bellow:
   
   -----
   -- split the given key
   create function dad_char_key_split(
           varchar,        -- char_key
           integer,        -- subkey_len
           char            -- separator
   ) returns varchar as '
   declare
           p_char_key      alias for $1;
           p_subkey_len    alias for $2;
           p_separator     alias for $3;
           v_key_len       integer;
           v_from          integer;
           v_result        varchar;
           v_sep           char;
   begin
           v_result := '''';
           v_sep := '''';
           v_from := 1;
           v_key_len := char_length(p_char_key);
           for i in 1..(v_key_len/p_subkey_len) loop
                   v_result := v_result || v_sep ||
   substr(p_char_key,v_from,p_subkey_len);
                   v_sep := p_separator;
                   v_from := v_from + p_subkey_len;
           end loop;
           return v_result;
   end;' language 'plpgsql';
   ----
   
   if I try this:
   
   select dad_char_key_split('00kjoi',2,',');
   
   I get this result:
   
   ",kj,oi"
   
   And when I change the initialization of the variables "v_sep" and
   "v_result" from empty strings to a space ('' '' istead of '''') I get
   the expected result:
   
   "00,kj,oi"
   
   It seems that plpgsql treats empty strings as null so when concatenating
   with a empty string we get null instead of some value.
   
   

2. Re: maybe a bug in plpgsql, nulls and empty strings are not the same

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-28T16:02:48Z

   Domingo Alvarez Duarte <domingo@dad-it.com> writes:
   > When trying write a function in plpgsql I'm getting behavior that
   > probably isn't the corect one.
   
   It works as expected if you declare v_sep as varchar rather than char.
   
   I think plpgsql may be interpreting
   	v_sep           char;
   as declaring v_sep to be the internal 1-byte "char" type, not char(n)
   with unspecified length as you are expecting.  There's definitely
   something strange going on with the assignment
   	v_sep := '''';
   
   In any case it's a tad bizarre to use char rather than varchar for
   something that you intend to have varying width, no?
   
   			regards, tom lane