Skip site navigation (1) Skip section navigation (2)

Re: Cool PL/PgSQL hack :)

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: David Fetter <david(at)fetter(dot)org>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Cool PL/PgSQL hack :)
Date: 2003-05-29 23:34:09
Message-ID: 20030529233409.GI62688@perrin.int.nxad.com (view raw or flat)
Thread:
Lists: sfpug
> 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

In response to

Responses

sfpug by date

Next:From: David FetterDate: 2003-05-30 00:58:48
Subject: Re: Cool PL/PgSQL hack :)
Previous:From: Stephan SzaboDate: 2003-05-29 22:06:00
Subject: Re: Next Meeting Set ... June 25th

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group