How can I use a PSQL query result as an "object"?-CodePudding

I have 2 queries as follows:

// create temp table for the table with selected supplier IDENTITY

SELECT supplier_id, supplier_name
INTO TEMPORARY TABLE temp_supplier
FROM suppliers
WHERE supplier_id = 2;

// join contacts table with temp supplier TABLE

SELECT supplier_name, scontact_name, scontact_title
INTO TEMPORARY TABLE temp2
FROM temp_supplier
LEFT JOIN supplier_contacts ON supplier_contacts.supplier_id = temp_supplier.supplier_id ORDER BY scontact_id ASC;

Essentially I want to save the result of query 1 to use with other queries. Right now I'm using temporary tables but I'm concerned because the WHERE clause in query one is to be used with Express JS so it will look something along the lines of:

SELECT supplier_id, supplier_name
INTO TABLE temp_supplier
FROM suppliers
WHERE supplier_id = $1;

So if that query has to be run multiple times what happens with the temporary tables? Any help at all is appreciated!

CodePudding user response：

You can create a function instead of a temporary table for query one :

CREATE OR REPLACE FUNCTION query1 (INOUT supplier_id integer, OUT supplier_name text)
RETURNS setof record LANGUAGE sql AS
$$
SELECT s.supplier_id, s.supplier_name
FROM suppliers AS s
WHERE s.supplier_id = supplier_id ;
$$ ;

And then you can call the function query1 in any kind of query :

SELECT supplier_name, scontact_name, scontact_title
  INTO TABLE temp2
  FROM query1(2) AS temp_supplier
  LEFT JOIN supplier_contacts 
    ON supplier_contacts.supplier_id = temp_supplier.supplier_id 
 ORDER BY scontact_id ASC;