Re: Convert a text list to text array? Was: Denormalizing during

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