Unable to save a stored procedure which contains a temporary table creation and update query

Question

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:

Please advise

Answer 1

You have to specify LANGUAGE plpgsql rather than LANGUAGE sql.

The function does not consist of only SQL statements, and other than for PL/pgSQL, the SQL language validator parses the SQL statements and checks them for validity.