Message-ID: <3E64E93D.9050506@incentre.net>
Date: Tue, 04 Mar 2003 10:58:21 -0700
From: Guy Fraser <guy@incentre.net>
Organization: J I Reasearch
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
MIME-Version: 1.0
To: Edmund Lian <no.spam@address.com>
Cc: pgsql-sql@postgresql.org
Subject: Convert a text list to text array? Was: Denormalizing during
 select
References: <200302241433.49502.josh@agliodbs.com>
	<fhpl5v86vkhq26gifm9g4cb1kh2o2b6d6r@4ax.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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.


Guy

Edmund Lian wrote:
> Jeff and Josh,
> 
> I found this example in "Practical PostgreSQL"... will it do the job?
> 
> """
> The following example defines an aggregate function named sum(), for
> use with the text data type. This aggregate calls the
> textcat(text,text) function built into PostgreSQL to return a
> concatenated "sum" of all the text found in its input values:
> 
> booktown=# CREATE AGGREGATE sum ( BASETYPE = text,
> booktown(#                        SFUNC = textcat,
> booktown(#                        STYPE = text,
> booktown(#                        INITCOND = '' );
> CREATE
> booktown=# SELECT sum(title || ' ') FROM books WHERE title ~ '^L';
...snip...