Date: Sat, 9 Aug 2003 12:12:12 +0200
From: Bertrand Petit <pgsql-sql@phoe.frmug.org>
To: PostgreSQL-sql <pgsql-sql@postgresql.org>
Subject: Comparing arrays
Message-ID: <20030809121212.A71948@memo.frmug.org>
Mail-Followup-To: PostgreSQL-sql <pgsql-sql@postgresql.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5.1i


	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