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 leftEXCEPT 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
