RE: List Concatination [warning]

A word of warning for the newbies...

*Don't* create a function called textcat. You *will* be sorry. There's
already a textcat builtin and you kinda need it for || and whatnot.
(Yes, I found out the hard way...)

There might be a better way to recover, but I did a pg_dumpall, clobbered
the postgres DB files, re-inited, re-loaded... and that got me textcat
back.

A related question: how do you *display* a function? Obviously there's
CREATE and DROP, but I could find nothing the equivalent of "LOOK AT".
I want to be able to see the code!

Anyhow, in case a working example of some list catting SQL would be useful,
here's what I put together to list all the states in a "zone":

Table "newstate"
Attribute | Type | Modifier
-----------+-------------+----------
state | varchar(4) |
fullstate | varchar(40) |
zone | varchar(4) |
country | varchar(4) |

Table "zone_names"
Attribute | Type | Modifier
-----------+------------+----------
zoneid | varchar(4) | not null
zonename | varchar(8) | not null
Indices: zone_names_zoneid_key,
zone_names_zonename_key

CREATE FUNCTION commacat(text,text) RETURNS text AS '
begin
if $1 <> '''' then
return $1 || '', '' || $2 ;
else
return $2;
end if;
end;
' LANGUAGE 'plpgsql';

CREATE AGGREGATE catenate(
sfunc1=commacat,
stype1=text,
basetype=text,
initcond1=''
);

CREATE VIEW zones AS
SELECT newstate.zone AS id, zone_names.zonename AS zone_name,
catenate(newstate.fullstate) AS desc
FROM newstate, zone_names
WHERE newstate.zone = zone_names.zoneid
GROUP BY newstate.zone, zone_names.zonename;

The output looks like:

cwilkins=# select * from zones where id = 'Z1';
id | zone_name | desc
----+-----------+----------------------------------
Z1 | Zone 1 | Delaware, New York, Pennsylvania
(1 row)

Many thanks to those here who provided clues on how to do this.
I'm a happy camper! :)

-cw-

> -----Original Message-----
> From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
> Sent: Thursday, March 15, 2001 11:30 AM
> Cc: sqllist
> Subject: Re: [SQL] List Concatination
>
>
> Richard,
>
> I wanted to thank you for the concatination suggestion
> ... on testing,
> a custom aggregate *was* faster than procedural concatination ... much
> faster.
>
> > But - if you don't care about the order of contacts you can
> define an
> > aggregate function:
> >
> > create aggregate catenate(sfunc1=textcat, basetype=text,
> stype1=text, initcond1='');
> >
> > Then group by client and catenate(firstname || ' ' || lastname)
> >
> > You'll want to read the CREATE AGGREGATE page in the
> reference manual,
> > replace textcat with your own routine that adds a comma and
> you'll need
> > a finalisation routine to strip the final trailing comma.
>
> Actually, if you use a sub-select as your data source, you can control
> both the appearance and the order of the catenated values:
>
> SELECT client, catenate(con_name)
> FROM (SELECT client, (firstname || ' ' || lastname || ', '
> FROM contacts ORDER BY lastname ASC) AS con_list
> GROUP BY client;
>
> This seems to work pretty well.
>
> > Note that this is probably not a good idea - the ordering of the
> > contacts will not be well-defined. When I asked about this
> Tom Lane was
> > quite surprised that it worked, so no guarantees about
> long-term suitability.
>
> Hmmm ... this feature is very, very, useful now that I know how to use
> it. I'd love to see it hang around for future versions of
> PgSQL. Tom?
>
> -Josh Berkus
>
> --
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh(at)agliodbs(dot)com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>