[SQL] ERROR: function expression in FROM may not refer to other relations of same query level

[Joseph S]

I've seen this asked in the archives, but there was never any answer.

Supposed I have this table:

create temp table tempa (ids int[]);
insert into tempa SELECT ARRAY[1 , 2, 3];

Now how do I get output from that?  None of these work: (xunnest is my 
version of unnest since I'm using 8.2.x)



select * FROM xunnest(tempa.ids);

SELECT * FROM (select * FROM xunnest(tempa.ids) AS aa) AS ab, tempa;

SELECT * FROM ( select * FROM xunnest(ta.ids) AS aa) AS ab, tempa ta;

SELECT * FROM tempa ta, ( select * FROM xunnest(ta.ids) AS aa) AS ab;

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] How do I search a two dimensional array?

[Joseph S]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql