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
