Postgresql – What to use instead of WITH ORDINALITY in PostgreSQL 9.3

postgresqlpostgresql-9.3

Issue

I have a query. Working with Postgres 11.5.

copy (
  select "HOBIS",
        "NAME",
        "URL",
        "IMG URL",
        "PRICE ORIGINAL"
  from (
    select distinct on ("HOBIS")
            product_shop_id as "HOBIS",
            product_name as "NAME",
            concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=email&utm_medium=Katalog&utm_campaign=Osvetleni_2019') as "URL",
            concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
            case
            when si_unit = 'KS' or si_unit = 'L' or si_unit = 'KG' or si_unit = 'M' then price_tax
            else si_unit_price_tax
            end as "PRICE ORIGINAL",
            x.idx
    from s_product
      join unnest(array['114260','114261','114262','114265','114266','114270','114268','114269','104978','104976','112293','106611','112294','106613','112295','107161','107162',
                        '107163','107164','112322','112323','344856','334980','334981','334983','334984','334982','320586','345784','345785','336117','346148','343216','343472','343217','343471',
                        '345999','343474','343475','346147','306651','306652','306653','306654','306655','306657','306658','306656','314203','314204','314200','314201','314202','346051','346052',
                        '346053','346054','346055','333900','333901','333903','317873','317874','317875','337981','310574','337982','303166','317877','303167','341962','341963','337150','337149',
                        '309973','309974','309975','309976','309978','309979','337944','337945','337946','337947','337948','321790','321791','321792','337147','337146','337145','337144','327099', .....]
                  ) with ordinality as x(shopid, idx) 
        on s_product.product_shop_id = x.shopid
      left join s_product_image on s_product.product_id = s_product_image.product_id
      left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
    where s_product.product_shop_id in (... same list as above ... )
      and image_order = '0'
     and s_pricelist_generated_lists.group_id = '1'
    order by "HOBIS"
    ) t
    order by t.idx
) to stdout (format csv, quote '"');

But it is not working with Postgres 9.3.24 (with ordinality was introduced in 9.4).

ERROR:  syntax error at or near "with ordinality"
LINE 303: ]) with ordinality as x(shopid, idx) on s_product.product_sh...
             ^

Is there some (easy) way to adjust this query for Postgres 9.3.24?

Best Answer

Instead of

SELECT unnest, ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]) WITH ORDINALITY;

you can also write

SELECT unnest, row_number() OVER () AS ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]);

Something like:

...
from s_product
  join (
    select unnest, row_number() over ()
    from unnest(array[....])
  ) as x(shopid, idx) 
    on s_product.product_shop_id = x.shopid
....
Related Question