Message-ID: <3F351AC9.6020606@joeconway.com>
Date: Sat, 09 Aug 2003 09:01:13 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Bertrand Petit <pgsql-sql@phoe.frmug.org>
Cc: PostgreSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: Comparing arrays
References: <20030809121212.A71948@memo.frmug.org>
In-Reply-To: <20030809121212.A71948@memo.frmug.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Bertrand Petit wrote:
> 	In two table I store "objects" and their attributes. The
> attributes, which are not meant to be searched, are stored as unbound
> arrays of varchars. I have a query that needs to use those attributes
> on both sides of an EXCEPT statement: 
> 
> 	SELECT left.id, left.attribs FROM left
> 	EXCEPT SELECT right.id, right.attribs FROM right;
> 
> That query can't be planed because of the following error: "Unable to
> identify an ordering operator '<' for type 'character varying[]'".

This should work on 7.4 beta.

create table myleft (id int, attribs varchar[]);
insert into myleft values(1,'{a,b}');
insert into myleft values(2,'{c,d}');
insert into myleft values(3,'{e,f}');
create table myright (id int, attribs varchar[]);
insert into myright values(2,'{c,d}');

regression=# SELECT myleft.id, myleft.attribs FROM myleft EXCEPT SELECT 
myright.id, myright.attribs FROM myright;
  id | attribs
----+---------
   1 | {a,b}
   3 | {e,f}
(2 rows)


> 	I thought that I could build such an operator using PL/pgSQL,
> unfortunately this language can't receive arguments of type ANYARRAY.

This also works on 7.4 beta.

> So this led me to the creation of a new ATTRIBUTES data type, the
> should be acceptable as an argument to a PL/pgSQL procedure.

Why do that -- I thought your data was in varchar[]? PL/pgSQL can have 
varchar[] as an argument in 7.3.

test=# create or replace function testplpgsql(varchar[], int) returns 
varchar as 'begin return $1[$2]; end;' language plpgsql;
CREATE FUNCTION
test=# select  testplpgsql('{a}'::varchar[],1); 
                                                  testplpgsql
-------------
  a
(1 row)

test=# select  testplpgsql('{a,b}'::varchar[],2);
  testplpgsql
-------------
  b
(1 row)

test=# select version();
                              version
-----------------------------------------------------------------
  PostgreSQL 7.3.3 on i686-redhat-linux-gnu, compiled by GCC 2.96
(1 row)


In any case, 7.3 and before have many issues with effective use of 
arrays. For instance, even if you got all this working, you'd find 
working with arrays in PL/pgSQL painful if not impossible.

If you really depend on arrays, I'd strongly suggest testing on 7.4 beta 
and moving to 7.4 as soon as it is released.

HTH

Joe