Postgresql : ERROR: in creating temp table with list object values

Question

I am writing a Postgresql function (using pgAdmin GUI) that will be invoked from my Spring Boot application with java list object as parameters

My actual requirement is mentioned here and with the suggestions to create a temp table with list values to optimize query, following is the Postgres function which I have tried by referring from here:

CREATE FUNCTION public."getInventory"("vals1Arg" character varying[], "vals2Arg" character varying[])
    RETURNS "INVENTORY"
    LANGUAGE 'sql'      
    
AS 
$BODY$
 
// I assume the below two lines create two temp tables and populate the table 
   with my list object values
CREATE TEMP TABLE t1 AS 
          SELECT * FROM VALUES(vals1Arg)

CREATE TEMP TABLE t2 AS 
          SELECT * FROM VALUES(vals2Arg)

SELECT * FROM "INVENTORY"
    where "COLUMN_1" in (select * from t1)
    and "COLUMN_2" in (Select * from t2);  
   
 $BODY$;

Following is the code snippet by how I am invoking postgres function

@Query(nativeQuery = true, value = "select \"getInventory\" (:vals1Arg,:vals2Arg)")
    List<Inventory> getInventoryInfo(List<String> vals1Arg, List<String> vals2Arg);

As the list is going to be huge, I am creating a temporary table with values from the list object paramaters and use it in my select query

Thank you in advance !!

Answer 1

There are several problems:

1. The syntax of the CREATE TABLE ... AS should be

   CREATE TABLE ... AS
      SELECT * FROM (VALUES (...)) AS alias;
   

   instead of

   CREATE TABLE ... AS
      SELECT * FROM VALUES (...);
   

   You need these parentheses and the alias.

2. The subselect in the query won't work, as it compares a varchar (COLUMN_1) with a varchar[] (the column of the temporary table).

   To make that work, you'd have to

   SELECT * FROM "INVENTORY"
      WHERE "COLUMN_1" = ANY (SELECT * FROM t1);
   

If you want to create temporary tables rather than using the array directly in the SELECT, you had better

CREATE TEMP TABLE t1 AS
   SELECT * FROM unnest(vals1Arg) AS u(c);
CREATE TEMP TABLE t2 AS
   SELECT * FROM unnest(scomoIdList) AS u(c);

ANALYZE t1, t2;

RETURN QUERY SELECT * FROM "INVENTORY"
                JOIN t1 ON "INVENTORY"."COLUMN_1" = t1.c
                JOIN t2 ON "INVENTORY"."COLUMN_2" = t2.c;

This assumes that the lists don't contain duplicate entries.