RE: List Concatination [warning]

<p><font size="2">A word of warning for the newbies...</font><p><font size="2">*Don't* create a function called
textcat. You *will* be sorry.  There's</font><br /><font size="2">already a textcat builtin and you kinda need it for
||and whatnot.</font><br /><font size="2">(Yes, I found out the hard way...)</font><p><font size="2">There might be a
betterway to recover, but I did a pg_dumpall, clobbered</font><br /><font size="2">the postgres DB files, re-inited,
re-loaded... and that got me textcat</font><br /><font size="2">back.</font><p><font size="2">A related question: how
doyou *display* a function?  Obviously there's</font><br /><font size="2">CREATE and DROP, but I could find nothing the
equivalentof "LOOK AT".</font><br /><font size="2">I want to be able to see the code!</font><p><font size="2">Anyhow,
incase a working example of some list catting SQL would be useful,</font><br /><font size="2">here's what I put
togetherto list all the states in a "zone":</font><p><font size="2">          Table "newstate"</font><br /><font
size="2"> Attribute|    Type     | Modifier</font><br /><font size="2">-----------+-------------+----------</font><br
/><fontsize="2"> state     | varchar(4)  |</font><br /><font size="2"> fullstate | varchar(40) |</font><br /><font
size="2"> zone     | varchar(4)  |</font><br /><font size="2"> country   | varchar(4)  |</font><p><font
size="2">       Table "zone_names"</font><br /><font size="2"> Attribute |    Type    | Modifier</font><br /><font
size="2">-----------+------------+----------</font><br/><font size="2"> zoneid    | varchar(4) | not null</font><br
/><fontsize="2"> zonename  | varchar(8) | not null</font><br /><font size="2">Indices: zone_names_zoneid_key,</font><br
/><fontsize="2">         zone_names_zonename_key </font><p><font size="2">CREATE FUNCTION commacat(text,text) RETURNS
textAS '</font><br /><font size="2">begin</font><br /><font size="2">        if $1 <> '''' then</font><br /><font
size="2">               return $1 || '', '' || $2 ;</font><br /><font size="2">        else</font><br /><font
size="2">               return $2;</font><br /><font size="2">        end if;</font><br /><font size="2">end;</font><br
/><fontsize="2">' LANGUAGE 'plpgsql';</font><p><font size="2">CREATE AGGREGATE catenate(</font><br /><font
size="2">       sfunc1=commacat,</font><br /><font size="2">        stype1=text,</font><br /><font size="2">       
basetype=text,</font><br/><font size="2">        initcond1=''</font><br /><font size="2">);</font><p><font
size="2">CREATEVIEW zones AS</font><br /><font size="2">  SELECT newstate.zone AS id, zone_names.zonename AS
zone_name,</font><br/><font size="2">        catenate(newstate.fullstate) AS desc</font><br /><font size="2">    FROM
newstate,zone_names</font><br /><font size="2">    WHERE newstate.zone = zone_names.zoneid</font><br /><font
size="2">   GROUP BY newstate.zone, zone_names.zonename; </font><p><font size="2">The output looks like:</font><p><font
size="2">cwilkins=#select * from zones where id = 'Z1';</font><br /><font size="2"> id | zone_name |              
desc</font><br/><font size="2">----+-----------+----------------------------------</font><br /><font size="2"> Z1 |
Zone1    | Delaware, New York, Pennsylvania</font><br /><font size="2">(1 row) </font><p><font size="2">Many thanks to
thosehere who provided clues on how to do this.</font><br /><font size="2">I'm a happy camper!  :)</font><p><font
size="2">-cw-</font><p><fontsize="2">> -----Original Message-----</font><br /><font size="2">> From: Josh Berkus
[<ahref="mailto:josh@agliodbs.com">mailto:josh@agliodbs.com</a>]</font><br /><font size="2">> Sent: Thursday, March
15,2001 11:30 AM</font><br /><font size="2">> Cc: sqllist</font><br /><font size="2">> Subject: Re: [SQL] List
Concatination</font><br/><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">>
Richard,</font><br/><font size="2">> </font><br /><font size="2">>       I wanted to thank you for the
concatinationsuggestion </font><br /><font size="2">> ... on testing,</font><br /><font size="2">> a custom
aggregate*was* faster than procedural concatination ... much</font><br /><font size="2">> faster.</font><br /><font
size="2">></font><br /><font size="2">> > But - if you don't care about the order of contacts you can
</font><br/><font size="2">> define an</font><br /><font size="2">> > aggregate function:</font><br /><font
size="2">>> </font><br /><font size="2">> > create aggregate catenate(sfunc1=textcat, basetype=text,
</font><br/><font size="2">> stype1=text, initcond1='');</font><br /><font size="2">> > </font><br /><font
size="2">>> Then group by client and catenate(firstname || ' ' || lastname)</font><br /><font size="2">> >
</font><br/><font size="2">> > You'll want to read the CREATE AGGREGATE page in the </font><br /><font
size="2">>reference manual,</font><br /><font size="2">> > replace textcat with your own routine that adds a
commaand </font><br /><font size="2">> you'll need</font><br /><font size="2">> > a finalisation routine to
stripthe final trailing comma.</font><br /><font size="2">> </font><br /><font size="2">> Actually, if you use a
sub-selectas your data source, you can control</font><br /><font size="2">> both the appearance and the order of the
catenatedvalues:</font><br /><font size="2">> </font><br /><font size="2">> SELECT client,
catenate(con_name)</font><br/><font size="2">> FROM (SELECT client, (firstname || ' ' || lastname || ', '</font><br
/><fontsize="2">>       FROM contacts ORDER BY lastname ASC) AS con_list</font><br /><font size="2">> GROUP BY
client;</font><br/><font size="2">> </font><br /><font size="2">> This seems to work pretty well.</font><br
/><fontsize="2">> </font><br /><font size="2">> > Note that this is probably not a good idea - the ordering of
the</font><br/><font size="2">> > contacts will not be well-defined. When I asked about this </font><br /><font
size="2">>Tom Lane was</font><br /><font size="2">> > quite surprised that it worked, so no guarantees about
</font><br/><font size="2">> long-term suitability.</font><br /><font size="2">> </font><br /><font size="2">>
Hmmm... this feature is very, very, useful now that I know how to use</font><br /><font size="2">> it.  I'd love to
seeit hang around for future versions of </font><br /><font size="2">> PgSQL.  Tom?</font><br /><font size="2">>
</font><br/><font size="2">> -Josh Berkus</font><br /><font size="2">> </font><br /><font size="2">> --
</font><br/><font size="2">> ______AGLIO DATABASE SOLUTIONS___________________________</font><br /><font
size="2">>                                        Josh Berkus</font><br /><font size="2">>    Complete
informationtechnology      josh@agliodbs.com</font><br /><font size="2">>     and data management solutions      
(415)565-7293</font><br /><font size="2">>    for law firms, small businesses       fax  621-2533</font><br /><font
size="2">>    and non-profit organizations.       San Francisco</font><br /><font size="2">> </font><br /><font
size="2">>---------------------------(end of </font><br /><font size="2">>
broadcast)---------------------------</font><br/><font size="2">> TIP 6: Have you searched our list
archives?</font><br/><font size="2">> </font><br /><font size="2">> <a
href="http://www.postgresql.org/search.mpl"target="_blank">http://www.postgresql.org/search.mpl</a></font><br /><font
size="2">></font>