Re: join from array or cursor

On Fri, Aug 21, 2009 at 2:16 PM, Greg Stark<gsstark@mit.edu> wrote:
> On Fri, Aug 21, 2009 at 1:16 PM, John DeSoi<desoi@pgedit.com> wrote:
>>
>> Yes, this is the best I have come up with so far. I have a set returning
>> function which returns the key and the index number. The implementation with
>> a cursor looks like this:
>>
>> SELECT * FROM cursor_pk('c1') c LEFT JOIN foo ON (c.pk = foo.pk) ORDER BY
>> c.idx;
>
> This might be a circumstance where it's really best to just force the
> planner to use a particular plan. There's no way to tell it "the order
> of the array" without forcing an entirely unnecessary sort.

Of course immediately upon hitting send I did think of a way:

 SELECT (r).*
  FROM (SELECT (SELECT x FROM x WHERE a=id) AS r
          FROM unnest(array[1,2]) AS arr(id)
       ) AS subq;

--
greg
http://mit.edu/~gsstark/resume.pdf