Comparing arrays

From: Bertrand Petit <pgsql-sql(at)phoe(dot)frmug(dot)org>
To: PostgreSQL-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Comparing arrays
Date: 2003-08-09 10:12:12
Message-ID: 20030809121212.A71948@memo.frmug.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


Hello,

I advance in my postgres exploration and found something that
looks quite strange, or at least unexpected regarding the present
7.3.3 documentation.

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[]'".

I thought that I could build such an operator using PL/pgSQL,
unfortunately this language can't receive arguments of type ANYARRAY.
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.

When I tried to create such a datatype, using a query modelled
after the documentation examples:

CREATE TYPE attributes (INPUT=array_in, OUTPUT=array_out,
INTERNALLENGTH=VARIABLE, ELEMENT=VARCHAR);

I'm signaled that the array_out procedure is not defined: "ERROR:
TypeCreate: function array_out(attributes) does not exist". That error
sounds strange as the CREATE TYPE manual describes uniform array type
creation as illustrated above and that array_out() seems to exist as
shown bellow.

SELECT proname, oidvectortypes(proargtypes) FROM pg_proc
WHERE proname LIKE 'array_%';

proname | oidvectortypes
---------------------+----------------------------
array_dims | anyarray
array_eq | anyarray, anyarray
array_in | cstring, oid, integer
array_length_coerce | anyarray, integer, boolean
array_out | anyarray
(5 rows)

All of this leads to the unavoidable questions:

1/ What went wrong with the ATTRIBUTES datatype creation?
How to correctly create it using 7.3.x backends?

2/ There may be better paths than creating a new datatype and
the associated operators that would permit using unbound
uniform arrays on both sides of an EXCEPT statement.
What would be such paths?

Regards.

--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2003-08-09 14:34:07 Re: Comparing arrays
Previous Message Josh Berkus 2003-08-08 21:45:44 Re: Abort Transaction DP PK (again)