varlena
varlena
PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

16-May-2005 Issue: 109

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see www.PostgreSQL.org.

Creating Array Arithmetic and Aggregates
Array Addition submitted by Tony Wasson 05-May-2005

Array arithmetic used to be difficult until all of the nice interfaces raised by the recent improvements by arrays. I once wrote these functions in C, but now they can be written more simply in plpgsql.

The array arithmetic function we have here will add two integer arrays together.

   select array_add('{1,2,3}', '{1,1,1}');
   array_add
   -----------
   {2,3,4}
If the arrays are different sized, the result will be the same size as the larger of the two making the assumption that shorter arrays should be treated as 0--an intersection model.
   select array_add('{1,2,3}', '{1,1,1,9}');
   array_add
   -----------
   {2,3,4,9}
Because the function is defined as STRICT, a NULL array will result in a NULL result. NULLS are not currently allowed as elements of any type of array. NULLS in an array will result in an input error.

Here is the function definition for array addition with an example run.

   CREATE TABLE arraytest (
   id character varying(10) NOT NULL,
   somearr integer[]
   );

   INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
   INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');

   CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS '
    DECLARE
      x ALIAS FOR $1;
      y ALIAS FOR $2;
      a int;
      b int;
      i int;
      res int[];
    BEGIN
      res = x;
   
      a := array_lower (y, 1);
      b := array_upper (y, 1);
   
      IF a IS NOT NULL THEN
        FOR i IN a .. b LOOP
          res[i] := coalesce(res[i],0) + y[i];
        END LOOP;
      END IF;

      RETURN res;
    END;
   ' LANGUAGE plpgsql STRICT IMMUTABLE;

The combination of the loop from a to b and the coalesce of the res value enable the arrays to be different sizes. Here are test results of the function with same sized arrays and different sized arrays.

   CREATE TABLE arraytest (
      id character varying(10) NOT NULL,
      somearr integer[]
   );

   INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
   INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');

   select * from arraytest;
    id | somearr
   ----+---------
    a  | {1,2,3}
    b  | {0,1,2}

   select array_add( somearr, '{1,1,1}' ) from arraytest;
    array_add
   -----------
    {2,3,4}
    {1,2,3}

   select array_add( somearr, '{1,1,1,1}' ) from arraytest;
    array_add
   -----------
    {2,3,4,1}
    {1,2,3,1}

   select array_add( somearr, '{1,1}' ) from arraytest;
    array_add
   -----------
    {2,3,3}
    {1,2,2}

Having a function to add arrays is very useful, but it can be even more useful if we use it to define an addition operator and a sum aggregate. Since the function is already written and tested it is only a matter of plugging them in.

In the first case we will add an + operator and tie it into the array_add function, specifying that the left and the right arguments are both integer arrays. Then we can just use the operator instead of explicitly invoking the function. For adding two literal arrays, you may have to explicitly cast one to ::int[] for the operator to be found.

   CREATE OPERATOR +  (
     PROCEDURE = array_add,
     LEFTARG = int[],
     RIGHTARG = int[]
   );

   select somearr + '{1,1,1,1}' from arraytest;
    ?column?
   -----------
    {2,3,4,1}
    {1,2,3,1}

Now we have a sensible way of adding arrays, we will want to sum arrays. We will define an aggregate named sum which will use the function array_add to operate on integer array types. This is overloading the sum() function so that now we can use it on these types.

   CREATE AGGREGATE sum (
      sfunc = array_add,
      basetype = INTEGER[],
      stype = INTEGER[],
      initcond = '{}'
   );
   
   select sum(somearr) from arraytest;
      sum
   ---------
    {1,3,5}

The obvious next steps include defining subtraction, multiplication and division for integer arrays in order to have a full set of arithmetic functions and operators. This is an exercise left to the reader ;)

The code used in this article can be found here. Contributors: Tony Wasson ajwasson at gmail.com, elein at varlena.com
Dropping and recreating Superusers
[GENERAL] alter table owner doesn't update acl information 10-May-2005

In a database there are superusers who own the views that are used by the ordinary users. The ordinary users do not access the underlying tables except through views.

As happens in the real world, one superuser left and another one was added. However, all of the views owned by the first superuser were inaccessible to users.

To prevent this problem from happening, you should create the new superuser with the same system identifyer as the old superuser. This way the new superuser will assume ownership of the views and there should be no access problems for the users. To create a new superusers with an existing sysid use:

	CREATE USER newpgadmin WITH SYSID 123;
This assumes that the uid of the old user was 123 and the new user's name is newpgadmin.

Contributors: Madeleine Theile madeleine.theile at cs.uni-dortmund.de Bruce Momjian pgman at candle.pha.pa.us Tom Lane tgl at sss.pgh.pa.us
Adding SERIAL primary keys to a table
[GENERAL] Mass Import/Generate PKs 06-Nov-2004

As part of a very large copy of data into a new table, a new PRIMARY KEY needed to be generated. In this example, an email list was to be imported.

The table is created and imported without a primary key using psql.

	create table emaillist as ( login text );
	\copy emaillist from '/data/files/email.data'
Then the primary key column and constraint can be added to the table using ALTER TABLE.
	ALTER TABLE emaillist ADD id SERIAL;
	ALTER TABLE emaillist ADD CONSTRAINT emaillist_pk PRIMARY KEY (id);
The particularly nice thing about adding a serial column is that the values are populated during the ALTER TABLE statement.

Contributors: Hunter Hillegas lists at lastonepicked.com, Peter Eisentraut peter_e at gmx.net, Franco Bruno Borghesi franco at akyasociados.com.ar, Ed L. pgsql at bluepolka.net, Goutam Paruchuri gparuchuri at oneil.com
General Bits
Missing Issue 15-May-2005

I humbly apologize for missing a new issue last week. What can I say? It is happens sometimes that work and life get in the way :)

Editor: elein at varlena.com


Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Top
Google
Search General Bits & varlena.com Search WWW