Re: update and group by/aggregate

2008/8/27 Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>:
> How can I update catalog_items.Authors
>
> create table catalog_items(
> ItemID bigint primary key,
> Authors varchar(1024)
> );
>
> taking results from
>
> select ia.ItemID, array_accum(trim(' \t]' from a.Name))
> from catalog_itemauthor ia
> join catalog_author a on a.AuthorID=ia.AuthorID
> where a.Name is not null and length(trim(' \t' from a.Name))>1
> group by ia.ItemID;
>
> Currently I'm achieving the same result with a plpsql function with
> a for loop, and I'm planning to see if using aggregates is going to
> be faster and then see if it is even worth to keep an extra column...
>
> create or replace function UpdateAuthors()
> returns void
> as
> $$
> declare
> _row record;
> _ItemID bigint;
> _Authors varchar(1024);
> _Name varchar(50);
> begin
> _Authors:='';
> _ItemID:=null;
> for _row in select a.Name, ia.ItemID from {catalog_itemauthor} ia
> join {catalog_author} a on a.AuthorID=ia.AuthorID
> order by ia.ItemID
> loop
> if(_row.ItemID<>_ItemID) then
> if(length(_Authors)>2) then
> _Authors:=substring(_Authors from 3);
> update {catalog_items} set Authors=_Authors
> where ItemID=_ItemID;
> end if;
> _Authors:='';
> end if;
> _ItemID:=_row.ItemID;
> _Name:=trim(' \t' from _row.Name);
> if(length(_Name)>0) then
> _Authors:= _Authors || ', ' || _Name;
> end if;
> end loop;
> return;
> end;
> $$ language plpgsql volatile;
>
> BTW
> I've noticed that array_append gives back not uniform results:
>
> select array_append(ARRAY['az','e','i'],'');
> {az,e,i,""}

it's correct, double quotes are used only for elements with some
speces or for empty string

if you would to print array content well, use array_to_string function

regards
Pavel Stehule

> while I'd expect
> {"az","e","i",""}
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>