update and group by/aggregate

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: update and group by/aggregate
Date: 2008-08-27 08:32:43
Message-ID: 20080827103243.66238e05@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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,""}
while I'd expect
{"az","e","i",""}

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2008-08-27 08:42:54 Re: update and group by/aggregate
Previous Message Teemu Juntunen 2008-08-27 08:24:17 ENABLE / DISABLE ALL TRIGGERS IN DATABASE