Re: join from array or cursor - Mailing list pgsql-general

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/