From netgeek@frigames.org Sun Aug 22 01:01:54 2004
Return-path: <netgeek@frigames.org>
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 <netgeek@speakeasy.net>
To: Mike Burns <netgeek@lube.frigames.org>
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" <netgeek@frigames.org>


--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--