2

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:

enter image description here

Please advise

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.