Convert a text list to text array? Was: [SQL] Denormalizing duringselect

[Guy Fraser]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly