Message-ID: <3E6500F4.8030700@joeconway.com>
Date: Tue, 04 Mar 2003 11:39:32 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Guy Fraser <guy@incentre.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Convert a text list to text array? Was: Denormalizing during
References: <200302241433.49502.josh@agliodbs.com>
	<fhpl5v86vkhq26gifm9g4cb1kh2o2b6d6r@4ax.com>
	<3E64E93D.9050506@incentre.net>
In-Reply-To: <3E64E93D.9050506@incentre.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Guy Fraser wrote:
> The email at the bottom gave me an idea, but it doesn't quite work:
> 
> CREATE AGGREGATE accumulate(
>   BASETYPE = text,
>   SFUNC = textcat,
>   STYPE = text,
>   INITCOND = '' );
> -- 
> SELECT ('{' || ltrim(accumulate(',' || tablename),',') || '}') as cruft
>  FROM pg_tables
>   WHERE hasindexes = 'f';
> 
>            cruft
> ---------------------------
>  {pg_xactlock,pg_listener}
> (1 row)
> 
> This produces somthing that looks like it could be able to be converted 
> into an array but I cant figure out how to make it work.
> 

If I understand correctly, the function array_accum() distributed with 
PL/R can do exactly what you're looking for (you need the function, but 
you don't necessarily need to use PL/R):

CREATE OR REPLACE FUNCTION array_accum (_name, name)
RETURNS name[]
AS '$libdir/plr','array_accum'
LANGUAGE 'C';

CREATE AGGREGATE accumulate (
   sfunc = array_accum,
   basetype = name,
   stype = _name
);

regression=# SELECT accumulate(tablename) as cruft FROM pg_tables WHERE 
tablename LIKE 'c%';
                  cruft
---------------------------------------
  {connectby_int,connectby_text,ct,cth}
(1 row)


See:
   http://www.joeconway.com/plr/doc/plr-aggregate-funcs.html
and download at:
   http://www.joeconway.com/plr/

HTH,

Joe