How to do SELECT UNNEST ARRAY on a list not stored in the db in django-CodePudding

I'm trying to reproduce this simplified Postgres query in Django (where [v1, v2, ...] is a python list):

SELECT * FROM UNNEST(ARRAY[v1, v2, ...]) objs (obj)
WHERE EXISTS(
  SELECT "table"."field1"
  FROM "table"
  WHERE "table"."field2" = 'value' AND "table"."field1" = fp
)

But I cannot find a way to use UNNEST(ARRAY(... on something that is not a table.

CodePudding user response：

For this i use common table expression (CTE) :

WITH cte AS
(
SELECT UNNEST(ARRAY[
v1,
v2,
......
])::int objs
)

then in query use RIGHT JOIN

full query:

WITH cte AS
(
SELECT UNNEST(ARRAY[
v1,
v2,
......
])::int objs
)

select *
from your_table as t
RIGHT JOIN cte ON cte.objs = t."your objects from table"

CodePudding user response：

ARRAY(subquery) is special array constructor that requires relation.

(2022-09-26 12:59:15) postgres=# select unnest(array(values(1),(2),(3)));
┌────────┐
│ unnest │
╞════════╡
│      1 │
│      2 │
│      3 │
└────────┘
(3 rows)