Temporary postgresql table function

Question

I cannot find a clear explanation of the syntax for creating (and using) tables only for internal function calculations. Can someone give me the syntax please?

From what I found, I tried this (with and without @ to temp_table ):

 CREATE FUNCTION test.myfunction() RETURNS SETOF test.out_table AS $$ DECLARE @temp_table TABLE ( id int, value text ) BEGIN INSERT INTO @temp_table SELECT id, value FROM test.another_table; INSERT INTO test.out_table SELECT id, value FROM @temp_table; RETURN END $$ LANGUAGE SQL; 

I get:

ERROR: syntax error at or near "DECLARE" LINE 5: DECLARE @temp_table TABLE

-

I also tried the CREATE TABLE approach suggested here , this way:

 CREATE FUNCTION test.myfunction() RETURNS SETOF test.out_table AS $$ CREATE TABLE temp_table AS SELECT id, value FROM test.another_table; INSERT INTO test.out_table SELECT id, value FROM temp_table; $$ LANGUAGE SQL; 

And I get this:

ERROR: relationship "temp_table" does not exist LINE 11: FROM temp_table

(Obviously, I know that temp_table is not required for what I do in the code above, but that’s not the point :) => I want to understand the syntax to make it work)

Answer 1

The appropriate syntax for creating a temp table is

 create temp table... 

but you must definitely drop the temp table before the function exists. In addition, I would suggest this syntax instead:

 CREATE TEMP TABLE IF NOT EXISTS temp_table AS SELECT id, value FROM test.another_table; 

So your function will look like this:

 CREATE FUNCTION test.myfunction() RETURNS SETOF test.out_table AS $$ CREATE TEMP TABLE IF NOT EXISTS temp_table AS SELECT id, value FROM test.another_table; INSERT INTO test.out_table SELECT id, value FROM temp_table; DROP TABLE temp_table; $$ LANGUAGE SQL; 

But if I can be so kind, I would like to rewrite this function so that it is more correct:

 CREATE FUNCTION test.myfunction() RETURNS TABLE (id int, value varchar) -- change your datatype as needed AS $$ BEGIN; CREATE TEMP TABLE IF NOT EXISTS temp_table AS SELECT id, value FROM test.another_table; INSERT INTO test.out_table SELECT id, value FROM temp_table; DROP TABLE temp_table; RETURN QUERY SELECT id, value from temp_table; END; $$ LANGUAGE plpgsql; 

untested; let me know if this fails.