I am unable to save my procedure in Postgres. The procedure contains a temporary table creation and update query.
Here is my query:
CREATE OR REPLACE PROCEDURE account_report_v2() LANGUAGE SQL AS $$ BEGIN; CREATE TEMP TABLE report_data on commit drop AS (select * from tests); update report_data set df_name = dfn, dl_name = dln From ( select id ,first_name dfn,last_name dln from df_info )AS subquery where (report_data.provider_id :: varchar(50)) = (subquery.id :: varchar(50)); COMMIT; $$; CALL account_report_v2();
When I try to save the procedure, postgres gives the following error:
relation "report_data" does not exist.
Here is how it looks like in pgAdmin 4: