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
