CODEWITHSUNDEEP
postgresqlpostgresql-10
CarlosS
CarlosS

Reputation: 181

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

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?

Upvotes: 1

Views: 607

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248245

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.

Upvotes: 2

Related Questions