From netgeek@frigames.org Sun Aug 22 01:01:54 2004 Return-path: Envelope-to: netgeek@lube.frigames.org Delivery-date: Sun, 22 Aug 2004 01:01:54 -0400 Received: from netgeek by frigames.org with local (Exim 3.36 #1) id 1BykUM-0001Hx-00 for netgeek@lube.frigames.org; Sun, 22 Aug 2004 01:01:54 -0400 Date: Sun, 22 Aug 2004 01:01:54 -0400 From: Mike Burns To: Mike Burns Subject: Second one Message-ID: <20040822050154.GB22771@lube.frigames.org> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="r5Pyd7+fXNt84Ff3" Content-Disposition: inline User-Agent: Mutt/1.4.1i Sender: "Mike Burns,,,617-739-1575" --r5Pyd7+fXNt84Ff3 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Mike, Next one. With an attachment. -- Mike Burns netgeek@speakeasy.net http://mike-burns.com --r5Pyd7+fXNt84Ff3 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="joinstr.sql" -- -- ~/projects/postgres/joinstr/joinstr.sql --- -- -- $Id: joinstr.sql,v 1.2 2004/03/28 01:44:58 harley Exp $ -- -- Commentary: -- * Join string aggregate for postgres. -- * We would really like to be able to say: -- select joinstr(col,':') from tablename; -- but postgres only permits aggregates of 'arity 1'. -- clear out drop aggregate joinstr; drop function joinstr_func(text,text); -- create or replace function joinstr_func(text,text) returns text as ' declare str1 alias for $1; str2 alias for $2; out text; begin if str1 is null then return str2; end if; if str2 is null then return str1; end if; return str1 || '' & '' || str2; end; ' language 'plpgsql'; -- tests -- select joinstr_func( 'a', 'b'); -- select joinstr_func(null, 'b'); -- select joinstr_func( 'a',null); -- Put the strings together create aggregate joinstr ( basetype = text, sfunc = joinstr_func, stype = text ); -- drop table foo; -- create table foo ( bar text ); -- insert into foo values ( 'a' ); -- insert into foo values ( 'b' ); -- insert into foo values ( 'c' ); -- insert into foo values ( null ); -- insert into foo values ( 'e' ); -- -- select joinstr(bar) from foo; --r5Pyd7+fXNt84Ff3--