pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConvert a text list to text array? Was: Denormalizing during select
4+ messages / 3 participants
[nested] [flat]
* Convert a text list to text array? Was: Denormalizing during select
@ 2003-03-04 17:58 Guy Fraser <guy@incentre.net>
0 siblings, 2 replies; 4+ messages in thread
From: Guy Fraser @ 2003-03-04 17:58 UTC (permalink / raw)
To: Edmund Lian <no.spam@address.com>; +Cc: 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...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert a text list to text array? Was: Denormalizing during select
@ 2003-03-04 18:27 Greg Stark <gsstark@mit.edu>
parent: Guy Fraser <guy@incentre.net>
1 sibling, 0 replies; 4+ messages in thread
From: Greg Stark @ 2003-03-04 18:27 UTC (permalink / raw)
To: Guy Fraser <guy@incentre.net>; +Cc: Edmund Lian <no.spam@address.com>; pgsql-sql
You might be able to adapt the code in contrib/intagg to handle text.
I'm not sure what would be involved in doing so.
--
greg
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert a text list to text array? Was: Denormalizing during
@ 2003-03-04 19:39 Joe Conway <mail@joeconway.com>
parent: Guy Fraser <guy@incentre.net>
1 sibling, 1 reply; 4+ messages in thread
From: Joe Conway @ 2003-03-04 19:39 UTC (permalink / raw)
To: Guy Fraser <guy@incentre.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Convert a text list to text array? Was: Denormalizing during
@ 2003-03-18 18:55 Guy Fraser <guy@incentre.net>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 4+ messages in thread
From: Guy Fraser @ 2003-03-18 18:55 UTC (permalink / raw)
To: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-18 18:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-04 17:58 Convert a text list to text array? Was: Denormalizing during select Guy Fraser <guy@incentre.net>
2003-03-04 18:27 ` Greg Stark <gsstark@mit.edu>
2003-03-04 19:39 ` Re: Convert a text list to text array? Was: Denormalizing during Joe Conway <mail@joeconway.com>
2003-03-18 18:55 ` Re: Convert a text list to text array? Was: Denormalizing during Guy Fraser <guy@incentre.net>
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