Re: Comparing arrays

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