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