Re: Denormalizing during select

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;
returnaccum; 
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