X-Original-To: pgsql-sql@postgresql.org Received: from bach.ccinet.ab.ca (bach.ccinet.ab.ca [198.161.96.1]) by postgresql.org (Postfix) with ESMTP id 3412B476321 for ; Tue, 18 Mar 2003 13:55:14 -0500 (EST) Received: from incentre.net (squid.incentre.net [206.75.213.195]) by bach.ccinet.ab.ca (8.12.6/8.12.6) with ESMTP id h2IItJjf056231 for ; Tue, 18 Mar 2003 11:55:20 -0700 (MST) (envelope-from guy@incentre.net) Message-ID: <3E776B8E.9000603@incentre.net> Date: Tue, 18 Mar 2003 11:55:10 -0700 From: Guy Fraser User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Convert a text list to text array? Was: Denormalizing during References: <200302241433.49502.josh@agliodbs.com> <3E64E93D.9050506@incentre.net> <3E6500F4.8030700@joeconway.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/445 X-Sequence-Number: 12484 Thanks a lot Joe Conway wrote: > 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 > >