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)

Postgres uses temporary tables for this purpose. Table variables are a feature of SQL Server. — Gordon Linoff
– Gordon Linoff, Commented Mar 18, 2016 at 17:52
Where in the manual did you find the syntax DECLARE @temp_table TABLE...? — user330315
– user330315, Commented Mar 21, 2016 at 17:48

François M. · Accepted Answer · 2016-03-22 01:10:52Z

27

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.

edited Mar 22, 2016 at 1:10

François M.

4,30711 gold badges36 silver badges87 bronze badges

answered Mar 18, 2016 at 19:42

dizzystar

1,08512 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

François M. Over a year ago

I'll test it at work tomorrow and will let you know (and accept it if it works), in the meantime, I just wanted to thank you for taking the time :)

François M. Over a year ago

I get a syntax error because of the CREATE statement. More precisely, I get the following : ERROR: syntax error at or near "create" // LINE 4: create TEMP TABLE temp_mag_ref_compl AS // ********** Erreur ********** // ERROR: syntax error at or near "create" // État SQL :42601 // Caractère : 114

dizzystar Over a year ago

yeah, you are right. I changed it to add "begin;" and "end;" That should prevent the syntax error.

dizzystar Over a year ago

@Wildcard If you keep the transactions open, the temp table can persist, throwing a "table already exists" error.

Dan Over a year ago

@dizzystar You appear to be dropping the temporary table and then after that selecting it for the RETURN QUERY ... is that correct, wouldn't it not exist?

|

Collectives™ on Stack Overflow

Temporary table postgresql function

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related