pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Josh Berkus <josh@agliodbs.com>
To: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: Re: Denormalizing during select
Date: Tue, 25 Feb 2003 10:24:19 -0800
Message-ID: <200302251024.19249.josh@agliodbs.com> (raw)
In-Reply-To: <988d394c6d80ecd1396eac17ff787470@news.teranews.com>
References: <u3kk5v4i276aqv2hv22pn6jh4t3a2ev08g@4ax.com>
	<988d394c6d80ecd1396eac17ff787470@news.teranews.com>

Jeff,

> > I have two tables (A and B) in a one-to-many relationship. When
> > retrieving data from one table (A), I need to denormalize the tables
> > during a select so that values of a column in the many table (B) appear
> > as a list in one field of the output.
> 
> Well, the straightforward way to do this would be with a function:

Actually, it's much easier to do this using a custom aggregate:

CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 = '''' THEN $1
	WHEN $1 IS NULL OR $1 = '''' THEN $2
	ELSE $1 || '', '' || $2
	END
' LANGUAGE 'sql';


CREATE AGGREGATE comma_list ( BASETYPE = text, SFUNC = comma_cat, STYPE = 
text,
INITCOND = '' );

SELECT a.id, a.col1, a.col2, comma_list(b.text)
FROM a JOIN b on a.id = b.a_id
GROUP BY a.id, a.col1, a.col2

The only drawback of this approach is that you cannot order the items in the 
list, but it is *much* faster than the function method that Jeff outlined.


-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco

view thread (11+ messages)  latest in thread

Message-ID: <200302251024.19249.josh@agliodbs.com>
Permalink:  ../200302251024.19249.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200302251024.19249.josh@agliodbs.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, jboes@nexcerpt.com
  Subject: Re: Denormalizing during select
  In-Reply-To: <200302251024.19249.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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