From: Joseph S <jks@selectacast.net>
Subject: How do I search a two dimensional array?
Date: Mon, 29 Mar 2010 18:17:11 -0400
Organization: Hub.Org Networking Services
Lines: 38
Message-ID: <hor8t7$13bo$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.23 (X11/20090812)
To: pgsql-sql@postgresql.org

I can search a one dimensional array with SELECT value = ANY(array), but 
how do I search  two dimensional array for a particular row?  ANY seems 
to flatten out a two dimensional array.

[local]:playpen=> select 2 = any (ARRAY[[1,7],[4,2]]);
  ?column?
----------
  t
(1 row)

Time: 52.451 ms
[local]:playpen=> select ROW(2,7) = any (ARRAY[[1,7],[4,2]]);
ERROR:  operator does not exist: record = integer
LINE 1: select ROW(2,7) = any (ARRAY[[1,7],[4,2]]);
                         ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.
[local]:playpen=> select ROW(4,2) = any (ARRAY[[1,7],[4,2]]);
ERROR:  operator does not exist: record = integer
LINE 1: select ROW(4,2) = any (ARRAY[[1,7],[4,2]]);
                         ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.
[local]:playpen=>
[local]:playpen=>
[local]:playpen=> select ARRAY[2,7] = ANY( ARRAY[[1,7],[4,2],[6,8]]);
ERROR:  operator does not exist: integer[] = integer
LINE 1: select ARRAY[2,7] = ANY( ARRAY[[1,7],[4,2],[6,8]]);
                           ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.
[local]:playpen=> select ARRAY[1,7]  = any ( ARRAY[[1,7],[4,2],[6,8]]);
ERROR:  operator does not exist: integer[] = integer
LINE 1: select ARRAY[1,7]  = any ( ARRAY[[1,7],[4,2],[6,8]]);
                            ^
HINT:  No operator matches the given name and argument type(s). You 
might need to add explicit type casts.
[local]:playpen=>