From: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: Re: Denormalizing during select
Date: Mon, 24 Feb 2003 19:12:03 GMT
Message-ID: <988d394c6d80ecd1396eac17ff787470@news.teranews.com> (raw)
References: <u3kk5v4i276aqv2hv22pn6jh4t3a2ev08g@4ax.com>
On Mon, 24 Feb 2003 12:18:24 -0500, no.spam wrote:
> 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:
create or replace function my_func(text) returns text as '
declare
name_one alias for $1;
accum text;
result record;
begin
accum := \'\';
for result in select property from many_table where name = name_one
loop
if accum != \'\'
then
accum := accum || \',\';
end if;
accum := accum || result.property;
end loop;
return accum;
end;
' language 'plpgsql';
Note that the quotes internal to the function must be escaped.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
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: jboes@nexcerpt.com
Subject: Re: Denormalizing during select
In-Reply-To: <988d394c6d80ecd1396eac17ff787470@news.teranews.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