Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group