update and group by/aggregate

Поиск
Список
Период
Сортировка
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "Teemu Juntunen"
Дата:
Сообщение: ENABLE / DISABLE ALL TRIGGERS IN DATABASE
Следующее
От: "Pavel Stehule"
Дата:
Сообщение: Re: update and group by/aggregate