Re: Is it possible to make the order of output the same as the order of input parameters? - Mailing list pgsql-general

On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski wrote:
> Hi,
>
> I have a simple query like:
>
> SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10)
>
> The problem is that I need to retrieve the rows in the same order as
> the set of ids provided in the select statement.  Can it be done?

Sure, but it can be a little cumbersome to set up at first.

WITH
    t(a) AS (VALUES (ARRAY[23, 56, 2, 12, 10])),
    s(i) AS (SELECT generate_subscripts((SELECT a FROM t)::integer[], 1))
SELECT i, a[i]
FROM s CROSS JOIN t;

will give you the indexes along with the elements, and you can then
sort by those.  If you happen to know in advance that you'll only have
integers, you can do this:

CREATE OR REPLACE FUNCTION index_list(integer[])
RETURNS TABLE(i integer, e integer)
LANGUAGE SQL
AS $$
WITH
    t(a) AS (VALUES ($1)),
    s(i) AS (SELECT generate_subscripts((SELECT a FROM t)::integer[], 1))
SELECT i, a[i]
FROM
    s
CROSS JOIN
    t;
$$;

You can then use that set-returning function in your query.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate