Search Postgresql Archives

Re: update and group by/aggregate

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2008/8/27 Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux