RE: List Concatination [warning] - Mailing list pgsql-sql
| From | Wilkinson Charlie E |
|---|---|
| Subject | RE: List Concatination [warning] |
| Date | |
| Msg-id | 36E747F41E90D411A6CC0004AC3699EB035F21B8@nct0010mb03.nc.no.irs.gov Whole thread Raw |
| List | pgsql-sql |
<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>