What to use instead of WITH ORDINALITY in PostgreSQL 9.3

Question

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?

• user1822: Unrelated, but: the where s_product.product_shop_id in (... ) with the same IDs as the ones in the array is useless. The JOIN already filters those rows.
• genderbee: @a_horse_with_no_name Thanks.

Answer 1

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
....