Ability to check dynamic sqls - #119 continuation

[plotn]

Hi! may I add some thoughts?

I tested pragma, and I think it could be useful here, e.g.:

v = 'select a from b';
PERFORM 'PRAGMA:check:' || v; -- if 'check' could be implemented
v = v || ' ORDER BY dynamically_added_sorting';
execute v;

But! Pragma works in specific way -
perform 'PRAGMA:echo:select a from b'; -- this works

-- this doesnt
t = 'PRAGMA:echo:select a from b';
PERFORM t;

Maybe this because of you said that assigments do not calculate while checking.
Is any workaround?

[okbob]

st 31. 8. 2022 v 10:30 odesílatel plotn ***@***.***> napsal:

Hi! may I add some thoughts? I tested pragma, and I think it could be useful here, e.g.: v = 'select a from b'; PERFORM 'PRAGMA:check:' || v; -- if 'check' could be implemented v = v || ' ORDER BY dynamically_added_sorting'; execute v; But! Pragma works in specific way - perform 'PRAGMA:echo:select a from b'; -- this works -- this doesnt t = 'PRAGMA:echo:select a from b'; PERFORM t; Maybe this because of you said that assigments do not calculate while checking. Is any workaround?

No. You have two requests a) assign value to variable in check time (compile time) b) check so this value is the same in runtime - so your request combines check and runtime together, and that is a problem. Theoretically, I can do PRAGMA check if the variable is marked as immutable some like $$ DECLARE CONSTANT _sqlconst varchar DEFAULT 'SELECT * FROM tab'; BEGIN PERFORM 'PRAGMA:sqlchecksyntax:_sqlconst'; ... This makes sense from a static analysis perspective and it can be implemented - more the CONSTANT mark ensures that the checked value will be used in runtime. But the dynamic SQL is a problem and will be a problem - and probably the best thing you can do is some combination of assertions and coverage analysis. You can use function CREATE OR REPLACE FUNCTION public.check_sql(character varying) RETURNS text LANGUAGE plpgsql AS $function$ begin execute 'explain ' || $1; return true; exception when others then return false; end; $function$ and then in your code can use ASSERT statement (2022-08-31 14:04:15) postgres=# do $$ declare v varchar = 'select * from pg_classx'; begin assert check_sql(v); end; $$; ERROR: assertion failed CONTEXT: PL/pgSQL function inline_code_block line 4 at ASSERT (2022-08-31 14:04:22) postgres=# do $$ declare v varchar = 'select * from pg_class'; begin assert check_sql(v); end; $$; DO So in this case (when you use really dynamic SQL generated in runtime), I think the best solution is using assertions. plpgsql_check supports coverage analyze and profiling - it can shows if your regress tests are enough or not

…

— Reply to this email directly, view it on GitHub <#121>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO43PQP6FEV26INRKZMLV34JZRANCNFSM6AAAAAAQBERTGQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[plotn]

brilliant idea, I think. thank you!
But, the idea with constant also attracts me (our 'dynamic' SQL is not so dynamic - we want to just construct 'WHERE' and 'ORDER BY', making other things quite static)

[plotn]

by the way - MR into DBEaver is here:
github.com/dbeaver/dbeaver/pull/17483

[okbob]

st 31. 8. 2022 v 14:59 odesílatel plotn ***@***.***> napsal:

by the way - MR into DBEaver is here: github.com/dbeaver/dbeaver/pull/17483 <dbeaver/dbeaver#17483>

great, thank you for info

…

— Reply to this email directly, view it on GitHub <#121 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO4554KBVQFQTMOMFOCTV35JKJANCNFSM6AAAAAAQBERTGQ> . You are receiving this because you commented.Message ID: ***@***.***>

[okbob]

st 31. 8. 2022 v 14:57 odesílatel plotn ***@***.***> napsal:

brilliant idea, I think. thank you! But, the idea with constant also attracts me (our 'dynamic' SQL is not so dynamic - we want to just construct 'WHERE' and 'ORDER BY', making other things quite static)

I am looking for PR :) There is another possibility - you can write query builder - and this can produce only valid SQL

…

— Reply to this email directly, view it on GitHub <#121 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEFO4747FR4CDFGL2MA63DV35JE3ANCNFSM6AAAAAAQBERTGQ> . You are receiving this because you commented.Message ID: ***@***.***>

[plotn]

I am looking for PR :)
I'm not very good in C, but, maybe it could be possible, who knows )))