From: | Guy Fraser <guy(at)incentre(dot)net> |
---|---|
To: | Edmund Lian <no(dot)spam(at)address(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Convert a text list to text array? Was: Denormalizing during select |
Date: | 2003-03-04 17:58:21 |
Message-ID: | 3E64E93D.9050506@incentre.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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...
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2003-03-04 18:16:41 | Re: Gist indexes on int arrays |
Previous Message | Fernando | 2003-03-04 17:44:44 | SETOF |