False error on temp table error:42P01:46:SQL statement:relation "blahblahblah" does not exist

[StepanYankevych]

plpgsql_check version 2.7 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit

In case temporary table is created inside the fucntion we check, it generates error error:42P01:46 anyway Is it possible to at least skip temporary tables create inside the fucntion/procedure?

Thanks! Stepan.

[okbob]

čt 2. 5. 2024 v 12:13 odesílatel StepanYankevych @.***> napsal:

plpgsql_check version 2.7 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Hat 11.4.1-2), 64-bit

In case temporary table is created inside the fucntion we check, it generates error error:42P01:46 anyway Is it possible to at least skip temporary tables create inside the fucntion/procedure?

please, can you show an example?

https://github.com/okbob/plpgsql_check#temporary-tables

Thanks! Stepan.

— Reply to this email directly, view it on GitHub https://github.com/okbob/plpgsql_check/issues/171, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO45XJPW7MPSGVGWCLM3ZAIGTPAVCNFSM6AAAAABHDNL7X2VHI2DSMVQWIX3LMV43ASLTON2WKOZSGI3TKMJSGMZDINI . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[StepanYankevych]

Here are steps to reproduce

`CREATE OR REPLACE FUNCTION staging.f_test_temp_check() RETURNS numeric LANGUAGE plpgsql AS $function$ DECLARE l_cnt int;
BEGIN

create temp table test_temp_check as select * from pg_tables limit 0 ;

select count(1) into l_cnt from test_temp_check;

return 1;
END; $function$ ;

`

Then select * from plpgsql_check_function('staging.f_test_temp_check()')

output is following error:42P01:9:SQL statement:relation "test_temp_check" does not exist Query: select count(1) from test_temp_check -- ^

[okbob]

Hi

čt 9. 5. 2024 v 15:19 odesílatel StepanYankevych @.***> napsal:

Here are steps to reproduce

`CREATE OR REPLACE FUNCTION staging.f_test_temp_check() RETURNS numeric LANGUAGE plpgsql AS $function$ DECLARE l_cnt int; BEGIN

create temp table test_temp_check as select * from pg_tables limit 0 ;

select count(1) into l_cnt from test_temp_check;

return 1; END; $function$ ;

`

Then select * from plpgsql_check_function('staging.f_test_temp_check()')

output is following error:42P01:9:SQL statement:relation "test_temp_check" does not exist Query: select count(1) from test_temp_check -- ^

Unfortunately, the fix is not possible. plpgsql_check does static analysis and doesn't execute queries and statements. In this case there is possibility to detect types, but in most cases the temp tables are created outside the function, so I didn't support it generally. This is a simple use case, but mostly temp tables are created outside the function, or there can be more execution paths, and the correct check is not easy.

please, use pragma https://github.com/okbob/plpgsql_check#temporary-tables

— Reply to this email directly, view it on GitHub https://github.com/okbob/plpgsql_check/issues/171#issuecomment-2102648797, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO43EQRR3H7ET34LZDTLZBNZXDAVCNFSM6AAAAABHDNL7X2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBSGY2DQNZZG4 . You are receiving this because you commented.Message ID: @.***>