Q:

PostgreSQL two dimensional array intersection

is it possible with postgres tools to intersect two-dimensional arrays?
For instance I have:
id               arr
1    {{1,2}, {3,4}, {4,5}, {4,7}}
2    {{4,2}, {7,4}, {8,5}, {9,7}}

...
And I want to get all records that have {4,5} in theirs array, record id=1 here.
If I do something like:
select * from table where arr && '{4,5}'

I'll get both of those example records. It finds all records where are 4 and 5 are anywhere in the array - as if it was looking in fully extracted arrays, like {1,2,3,4,4,5,4,7}.

A:

I thought of using functions from the extension intarray, but:

Many of these operations are only sensible for one-dimensional arrays.
Although they will accept input arrays of more dimensions, the data is
treated as though it were a linear array in storage order.

My other ideas:
Quick and dirty
WITH x(id, arr) AS (VALUES
    (1, '{{1,2}, {3,4}, {4,5}, {4,7}}'::int[])
   ,(2, '{{4,2}, {7,4}, {8,5}, {9,7}}')
   )
SELECT * 
FROM   x
WHERE  arr::text LIKE '%{4,5}%';

The simple trick is to transform the array to its text representation and check with LIKE.
With array-functions
WITH x(id, arr) AS (
    VALUES
     (1, '{{1,2}, {3,4}, {4,5}, {4,7}}'::int[])
    ,(2, '{{4,2}, {7,4}, {8,5}, {9,7}}')
    )
    ,y AS (
    SELECT id, arr, generate_subscripts(arr, 1) AS i
    FROM   x
)
SELECT id, arr
FROM   y
WHERE  arr[i:i] = '{{4,5}}'::int[];

Or, the same in different notation, building on your example:
SELECT id, arr
FROM  (
    SELECT id, arr, generate_subscripts(arr, 1) AS i
    FROM   tbl
    ) x
WHERE  arr[i:i] = '{{4,5}}'::int[];

This generates the array subscripts for the first dimension. With the help of this set-returning function we check each array-slice. Note the double brackets on the right hand side of the expression to create a two-dimensional array that matches.
If {4,5} can be in arr multiple times, add GROUP BY or a DISTINCT clause to avoid duplicate rows.

