I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first
SELECT query to an array as well, so I can reuse the ID's inside another select query. I first tried to add an alias to the select query like
SELECT * FROM (SELECT id FROM products) as pr and use
pr inside the
NOT IN(pr) statement of the second query, but that doesn't work ...
I will explain it more clearly with an example, this is a simplified version of the function:
CREATE OR REPLACE FUNCTION featured_products( valid_to_in timestamp without time zone, taxonomy_id_in integer, product_limit_in integer) RETURNS SETOF integer AS $BODY$ BEGIN RETURN QUERY ( -- #1 SELECT * FROM ( SELECT "product"."supplier_id" FROM products AS "product" ) AS "featured" LIMIT 2 ) UNION ALL SELECT * FROM ( SELECT "product"."supplier_id" FROM products AS "product" ) AS "featured" WHERE id NOT IN ( -- #2 SELECT * FROM ( SELECT "product"."supplier_id" FROM products AS "product" ) AS "featured" LIMIT 2 ) LIMIT product_limit_in; END; $BODY$ LANGUAGE plpgsql VOLATILE;
I deleted some joins and
GROUP BY and
ORDER BY statements, so the function is a bit more readable. And I added
#2 inside the code above, so you know what I mean with select query 1 and 2.
As you can see the query #2 should return the same results as query #1. In reality these queries are much bigger. So you I just want to replace the second, identical query with just an array of ID's. Less code and probably faster.
I don't know how to add the IDs returned from the first query, to an array and put that in a
NOT IN(<id's>) statement instead the second query.
Anyone who does know how to fix this?