Temporary table postgresql function

Question

I can't find a clear explanation of the syntax to create (and use) tables just for the inside calculations of a function. Could anyone give me a syntax exemple please ?

From what I've found, I have tried this (with and without @ before 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: relation "temp_table " does not exist LINE 11: FROM temp_table

(Obviously, I'm aware the temp_table is not necessary for what I'm doing in the code above, but that's not the point :) => I want to understand the syntax to get it to work)

Answer 1

The appropriate syntax for creating a temp table is

create temp table...

but you have to be sure to drop the temp table before existing out of the function. Also, I'd suggest this syntax instead:

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

Thus your function will be 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'd like to rewrite this function so 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.