Re: join from array or cursor

On Thu, Aug 20, 2009 at 11:15:12PM -0400, John DeSoi wrote:
> Suppose I have an integer array (or cursor with one integer column)
> which represents primary keys of some table. Is there a simple and
> efficient way to return the rows of the table corresponding to the
> primary key values and keep them in the same order as the array (or
> cursor)?

You could do something like:

SELECT f.id
FROM foo f, (
SELECT i, myPkArray[i] AS elem
FROM generate_series(array_lower(myPkArray,1),array_upper(myPkArray,1)) i) x
WHERE f.id = x.elem
ORDER BY x.i;

It may help to wrap the generate_series call into a function so you
don't have to refer to "myPkArray" so many times.

--
Sam http://samason.me.uk/