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?
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…