From: Joe Conway <mail@joeconway.com>
To: Guy Fraser <guy@incentre.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Convert a text list to text array? Was: Denormalizing during
Date: Tue, 04 Mar 2003 11:39:32 -0800
Message-ID: <3E6500F4.8030700@joeconway.com> (raw)
In-Reply-To: <3E64E93D.9050506@incentre.net>
References: <200302241433.49502.josh@agliodbs.com>
<fhpl5v86vkhq26gifm9g4cb1kh2o2b6d6r@4ax.com>
<3E64E93D.9050506@incentre.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, guy@incentre.net
Subject: Re: Convert a text list to text array? Was: Denormalizing during
In-Reply-To: <3E6500F4.8030700@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox