Message-ID: <988d394c6d80ecd1396eac17ff787470@news.teranews.com>
Date: Mon, 24 Feb 2003 19:12:03 GMT
From: Jeff Boes <jboes@nexcerpt.com>
Subject: Re: Denormalizing during select
Organization: Nexcerpt, Inc.
References: <u3kk5v4i276aqv2hv22pn6jh4t3a2ev08g@4ax.com>
User-Agent: Pan/0.11.4 (Unix)
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Lines: 36
To: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable

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 :=3D \'\';
  for result in select property from many_table where name =3D name_one
loop
    if accum !=3D \'\'
    then
      accum :=3D accum || \',\';
    end if;
    accum :=3D accum || result.property;
  end loop;
  return accum;
end;
' language 'plpgsql';

Note that the quotes internal to the function must be escaped.

--=20
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise