Join the output of an array without creating a temporary table

Question

I have a query in UDF (shown below) in which unnest() array of integers and joins against it, I created a local temp table in my pgplsql UDF, since I know that this works. Is it possible to use unnest directly in a query to perform a join instead of creating a temporary table?

 CREATE OR REPLACE FUNCTION search_posts( forum_id_ INTEGER, query_ CHARACTER VARYING, offset_ INTEGER DEFAULT NULL, limit_ INTEGER DEFAULT NULL, from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, in_categories_ INTEGER[] DEFAULT '{}' ) RETURNS SETOF forum_posts AS $$ DECLARE join_string CHARACTER VARYING := ' '; from_where_date CHARACTER VARYING := ' '; to_where_date CHARACTER VARYING := ' '; query_string_ CHARACTER VARYING := ' '; offset_str_ CHARACTER VARYING := ' '; limit_str_ CHARACTER VARYING := ' '; BEGIN IF NOT from_date_ IS NULL THEN from_where_date := ' AND fp.posted_at > ''' || from_date_ || ''''; END IF; IF NOT to_date_ IS NULL THEN to_where_date := ' AND fp.posted_at < ''' || to_date_ || ''''; END IF; IF NOT offset_ IS NULL THEN offset_str_ := ' OFFSET ' || offset_; END IF; IF NOT limit_ IS NULL THEN limit_str_ := ' LIMIT ' || limit_; END IF; IF NOT limit_ IS NULL THEN END IF; CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ; if in_categories_ != '{}' THEN join_string := ' INNER JOIN un_cat uc ON uc.id = fp.category_id ' ; END IF; s query_string_ := ' SELECT fp.* FROM forum_posts fp' || join_string || 'WHERE fp.forum_id = ' || forum_id_ || ' AND to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' || from_where_date || to_where_date || offset_str_ || limit_str_ || ';'; RAISE NOTICE '%', query_string_; RETURN QUERY EXECUTE query_string_; END; $$ LANGUAGE plpgsql; select * from search_posts(forum_id_:=1, query_:='scout & rampage', in_categories_ := '{71}'); 

The reason why I want to do this is because I want to be able to take a look at the query plan without resorting to considering the output of the automatic explanation on the server log in . A local temporary table prevents this. Ie, I can’t copy paste the resulting query and execute it verbatim.

Answer 1

Is it possible to use unnest() directly in a query to perform a join instead of creating a temporary table?

Yes, and it is much more effective. ^{(If you do not need unaccounted data in several queries that cannot be combined together in (changing data) CTE.)}

Consider the following example:

 SELECT * FROM tbl t JOIN unnest('{10,11,12}'::int[]) AS x(tbl_id) USING (tbl_id);