From: Bertrand Petit <pgsql-sql@phoe.frmug.org>
To: PostgreSQL-sql <pgsql-sql@postgresql.org>
Subject: Comparing arrays
Date: Sat, 9 Aug 2003 12:12:12 +0200
Message-ID: <20030809121212.A71948@memo.frmug.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql-sql@phoe.frmug.org
Subject: Re: Comparing arrays
In-Reply-To: <20030809121212.A71948@memo.frmug.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox