Thread: Comparing arrays
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
Bertrand Petit <pgsql-sql@phoe.frmug.org> writes:
> That query can't be planed because of the following error: "Unable to
> identify an ordering operator '<' for type 'character varying[]'".
This shortcoming is (at long last) repaired for 7.4. There is no simple
solution in earlier releases, I fear.
> 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.
CREATE TYPE wants an *exact* match of the argument/result datatypes.
You could fake it out by creating another pg_proc row pointing at the
same internal procedure. However, I think creating a private datatype
is the hard way to go about this. A better short-run solution is just
to create = and < operators for varchar[].
regards, tom lane
Bertrand Petit wrote:
> 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
byGCC 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
On Sat, Aug 09, 2003 at 09:01:13AM -0700, Joe Conway wrote:
>
> This should work on 7.4 beta.
And now it works too in 7.3. I've implemented the <, <=, =,
<>, =>, and > operators in PL/pgSQL for the VARCHAR[] type and
assembled them into an operator class. This is just a bit slow but
that's bearable considering that the queries using them are just
exceptional administrativia tasks.
> 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.
The only annoyance trouble I had with array manipulation is
the array_dims() function. I had to wrap it inside
split_part(split_part(array_dims($1), ']', 1), ':', 2)::INT
> 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.
Right now I'll try to stay away from betas or .1 releases: I'm
not yet confident enough with my skills and may not be able to decide
if a failure is mine or postgresql's.
Thanks for your kind answer.
--
%!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