Re: Cool PL/PgSQL hack :)

> Kind people,
>
> Here's a little hack I put together that looks a lot like Perl's
> join() operator. Comments, suggestions for improvement, and sources
> for old-school pizzelle irons are all welcome :)

For the hyper performance sensitive, use := instead of SELECT INTO.

> CREATE OR REPLACE FUNCTION string_join(VARCHAR, VARCHAR[])
> RETURNS VARCHAR AS '
> DECLARE
> joiner ALIAS FOR $1;
> my_array ALIAS FOR $2;
> dimstring TEXT;
> joined_stuff TEXT;
> counter INTEGER;
> the_start INTEGER;
> the_end INTEGER;
> BEGIN
> SELECT INTO dimstring array_dims(my_array);

dimstring := array_dims(my_array);

> the_start := ltrim(split_part(dimstring, '':'', 1), ''['')::INTEGER;
> the_end := rtrim(split_part(dimstring, '':'', 2), '']'')::INTEGER;
> FOR counter IN the_start .. the_end LOOP
> IF counter = 1
> THEN
> joined_stuff := my_array[counter];
> ELSE
> joined_stuff := joined_stuff || joiner || my_array[counter];
> END IF;
> END LOOP;
> RETURN (joined_stuff);
> END;
> ' LANGUAGE 'plpgsql';

Avoiding using SELECT INTO saves you about .2ms on my laptop for the
1st invocation, and 0.05ms for every future invocation (the plans are
cached). Not huge, but given that in the last two weeks I've thumped
out just over 400 pl/pgsql functions... well, it all slowly adds up.

That said, I'm slowly moving things over to compiled .so's which is
significantly faster, but this had me wondering: has anyone done any
comparative benchmarks of the various pl languages for PostgreSQL?
-sc

--
Sean Chittenden