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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
