postgresql - Dropping Postgres schema inside plpgsql function after RETURN QUERY EXECUTE on a table within that schema

Question

I have a plpgsql function that creates a series of UNLOGGED tables in a schema. The function needs to drop this temporary schema after returning results from one of these tables

        RETURN QUERY EXECUTE 'SELECT * FROM scratch_schema.table'

        EXECUTE 'DROP SCHEMA IF EXISTS ' || scratch_schema || ' CASCADE';

        EXCEPTION WHEN others THEN
            EXECUTE 'DROP SCHEMA IF EXISTS ' || scratch_schema || ' CASCADE';
            RAISE;

        RETURN;

However when the function attempts to drop the schema I get the following message:

[2021-01-19 15:34:04] [XX000] ERROR: could not open relation with OID 124466

From what I can discern, Postgres is dropping the schema before the data is read from table. This doesn't seem like it should be possible, but I was able to confirm that if I remove the DROP SCHEMA ... line the function is able to execute and return correctly. Is there any mechanism to ensure the schema is properly dropped only after the results are sent?

Answer 1

Since you are dropping the tables anyway, I don't see the need for unlogged tables.

Use TEMPORARY tables instead of UNLOGGED. Those are dropped at the end of the session automatically. Or you can create them with ON COMMIT DROP to have them dropped at the end of the transaction. Cheaper, too.