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.

Sun, 27 May 2001 19:00:37 +0200
Domingo Alvarez Duarte <domingo@dad-it.com>
maybe a bug in plpgsql, nulls and empty strings are not the same
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

Mon, 28 May 2001 12:02:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: maybe a bug in plpgsql, nulls and empty strings are not the same 