How to return temp table result in postgresql function
I am using a temporary table in a function to save some results however I don't know how to return the table from the function. Ideally I would like to do everything in one query (i.e. not two queries: one for calling the function, the other to get data from the temp table).
Currently my main_function() is as follows:
CREATE OR REPLACE FUNCTION main_function() RETURNS void AS $BODY$ BEGIN DROP TABLE IF EXISTS temp_t CASCADE; CREATE TEMP TABLE temp_t AS SELECT * FROM tbl_t limit 0; EXECUTE 'INSERT INTO temp_t ' || 'SELECT * FROM tbl_t limit 10'; END; $BODY$ LANGUAGE 'plpgsql' ;
And I am calling it like so:
SELECT * from main_function(); SELECT * from temp_t;
Again, the problem is that I don't actually want to call the second query. The first query should return the temp table as a result, however I cannot do this since the temp table is created in main_function() so it cannot be its return type.
Any ideas on how to achieve this?
Inside your main_function():
RETURN QUERY SELECT * FROM temp_t;
...if temp_t table consists of e.g. column1 (type integer), column2 (boolean) and column3 (varchar(100)), you should also define returned type as:
CREATE OR REPLACE FUNCTION main_function(column1 OUT integer, column2 OUT boolean, column3 OUT varchar(100)) RETURNS SETOF record AS (...)
Another way is to define new data type:
CREATE TYPE temp_t_type AS ( column1 integer, column2 boolean, column3 varchar(100) );
That type can be returned by your functions in the same way as normal data types:
CREATE OR REPLACE FUNCTION main_function() RETURNS SETOF temp_t_type AS (...)
...and return result from the function in the same way as mentioned above.
Are you sure you need a temporary table? Most of the time, there is a cheaper solution. Your example can simply be:
CREATE OR REPLACE FUNCTION main_function() RETURNS SETOF tbl_t AS $BODY$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM tbl_t LIMIT 10'; END $BODY$ LANGUAGE plpgsql;
You also don't need EXECUTE or even plpgsql for the simple case:
CREATE OR REPLACE FUNCTION main_function() RETURNS SETOF tbl_t AS $BODY$ SELECT * FROM tbl_t LIMIT 10; $BODY$ LANGUAGE sql;
Never quote the language name. It's an identifier.
CREATE OR REPLACE FUNCTION main_function() RETURNS void AS
use some like CREATE OR REPLACE FUNCTION main_function() RETURNS TABLE(f1 int, f2 text) AS...