Re: concat_ws - Mailing list pgsql-admin

Tom Lane wrote:
> I think you'd need to generate a separate function definition for
> each number of arguments you wanted to deal with, which is a bit
> of a pain in the neck, but it still beats writing an extension
> function in C ...

I thought I'd whack out this example similar to the GREATEST/LEAST
functions a month or so ago. It works fine in 7.3, but has a problem on
7.4devel. First the function:

create or replace function make_concat_ws() returns text as '
declare
   v_args int := 32;
   v_first text := ''create or replace function
concat_ws(text,text,text) returns text as ''''select case when $1 is
null then null when $3 is null then $2 else $2 || $1 || $3 end''''
language sql IMMUTABLE'';
   v_part1 text := ''create or replace function concat_ws(text,text'';
   v_part2 text := '') returns text as ''''select
concat_ws($1,concat_ws($1,$2'';
   v_part3 text := '')'''' language sql IMMUTABLE'';
   v_sql text;
begin
   execute v_first;
   for i in 4 .. v_args loop
     v_sql := v_part1;
     for j in 3 .. i loop
       v_sql := v_sql || '',text'';
     end loop;

     v_sql := v_sql || v_part2;

     for j in 3 .. i - 1 loop
       v_sql := v_sql || '',$'' || j::text;
     end loop;
     v_sql := v_sql || ''),$'' || i::text;

     v_sql := v_sql || v_part3;
     execute v_sql;
   end loop;
   return ''OK'';
end;
' language 'plpgsql';

select make_concat_ws();

After creating and executing make_concat_ws(), you'll have 30
concat_ws() functions accepting from 3 to 32 arguments. On 7.3 it works
well:

test=# select

concat_ws('~','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31');
                                           concat_ws
----------------------------------------------------------------------------------------------

01~02~03~04~05~06~07~08~09~10~11~12~13~14~15~16~17~18~19~20~21~22~23~24~25~26~27~28~29~30~31
(1 row)

test=# explain analyze select

concat_ws('~','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31');
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00
rows=1 loops=1)
  Total runtime: 0.02 msec
(2 rows)

But on 7.4devel it works OK with smaller numbers of arguments, and seems
to take exponentially longer as arguments are added. The odd thing is
that explain analyze does not seem to reflect this. I noticed that on
7.4devel:

regression=# explain analyze select
concat_ws('~','01','02','03','04','05','06','07','08');
                                                      QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00
rows=1 loops=1)
  Total runtime: 0.05 msec
(2 rows)

regression=# explain analyze select
concat_ws('~','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16');
                                     QUERY PLAN
----------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
  Total runtime: 0.07 msec
(2 rows)

But the "clock" time to run the commands is noticeably longer for the 17
argument case (~2 seconds versus instant). At 25 arguments (possibly
sooner, I didn't test cases in between) it fails with:

regression=# select

concat_ws('~','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24');
ERROR:  ERRORDATA_STACK_SIZE exceeded

I don't have time at the moment to dig into this, but I'll try to later
today or tomorrow.

Joe