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

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;

Joseph S <jks@selectacast.net> writes:
> 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?

Uh, you didn't actually say what output you're looking for, but
I'm going to guess it's this:

regression=# select unnest(ids) from tempa;unnest 
--------     1     2     3
(3 rows)

Pre-8.4, you need a version of unnest() that's coded in C or SQL;
plpgsql won't do.
        regards, tom lane