Drop temporary table when exiting function that return a JSON in PostgreSQL

Question

I'm new on PostgreSQL and I want to know why my temporary table is not dropped after a return different to return query.

I can execute this function many times without a "table already exists" error.

CREATE OR REPLACE FUNCTION test_table()
   RETURNS TABLE (response JSON)
AS $$
BEGIN
    CREATE TEMP TABLE temp_test_table AS SELECT * FROM Users;
    RETURN QUERY SELECT '{"name": "Moises"}'::JSON;
    DROP TABLE temp_test_table;
END; $$
LANGUAGE 'plpgsql';

But in this case, after the first execution, I receive a "ERROR: relation "temp_test_json" already exists".

CREATE OR REPLACE FUNCTION test_json()
   RETURNS JSON
AS $$
BEGIN
    CREATE TEMP TABLE temp_test_json AS SELECT * FROM Users;
    RETURN '{"name": "Moises"}'::JSON;
    DROP TABLE temp_test_json;
END; $$
LANGUAGE 'plpgsql';

How return query or just return affects the temporary table when is dropped?

score 2 · Accepted Answer · answered Sep 03 '21 at 14:51

2

RETURN terminates the execution of the function, so execution does not reach the DROP TABLE statement in the second function.

Contrariwise, RETURN QUERY adds rows to the function result set, but does not terminate the execution, so DROP TABLE is executed in the first function.

answered Sep 03 '21 at 14:51

Laurenz Albe

209,280
17
206
263

Then, I assume the temporal table always is gonna be dropped before the function retrieves any data if the return query is not a final return. – CarlosS Sep 03 '21 at 22:03
No, the result is cached in memory. – Laurenz Albe Sep 06 '21 at 05:24

Drop temporary table when exiting function that return a JSON in PostgreSQL

1 Answers1