Re: proposal: plpgsql - Assert statement

2014-09-05 9:52 GMT+02:00 Marko Tiikkaja <marko(at)joh(dot)to>:

> On 2014-09-05 08:16, Pavel Stehule wrote:
>
>> Assert is usually implemented as custom functions and used via PERFORM
>> statement now
>>
>> -- usual current solution
>> PERFORM Assert(some expression)
>>
>> I would to implement Assert as plpgsql internal statement due bigger
>> possibilities to design syntax and internal implementation now and in
>> future. More - as plpgsql statement should be compatible with any current
>> code - because there should not be collision between SQL and PLpgSQL
>> space.
>> So this design doesn't break any current code.
>>
>
> It does require making ASSERT an unreserved keyword, no? That would break
> code where someone used "assert" as a variable name, for example.
>

sure, sorry

>
> I propose following syntax with following ecosystem:
>>
>> ASSERT [ NOTICE, WARNING, >>EXCEPTION<< ]
>> [ string expression or literal - explicit message ]
>> [ USING clause - same as RAISE stmt (possible in future ) ]
>> ( ROW_COUNT ( = | <> ) ( 1 | 0 ) |
>> ( QUERY some query should not be empty ) |
>> ( CHECK some expression should be true )
>> ( IS NOT NULL expression should not be null )
>>
>
> UPDATE tab SET c = 1 WHERE pk = somevar;
>> ASSERT ROW_COUNT = 1; -- knows what is previous DML or Dynamic DML
>> ASSERT CHECK a < 100;
>> ASSERT IS NOT NULL pk;
>> ASSERT QUERY SELECT id FROM tab WHERE x = 1;
>> ASSERT CHECK 2 = (SELECT count(*) FROM tab WHERE x = 1);
>>
>
> I don't see the need for specialized syntax. If the syntax was just
> ASSERT (<expr>), these could be written as:
>
> ASSERT (row_count = 1); -- assuming we provide a special variable instead
> of having to do GET DIAGNOSTICS
> ASSERT (a < 100); -- or perhaps ASSERT((a < 100) IS TRUE); depending on
> how NULLs are handled
> ASSERT (pk IS NOT NULL);
> ASSERT (EXISTS(SELECT id FROM tab WHERE x = 1));
> ASSERT (2 = (SELECT count(*) FROM tab WHERE x = 1));
>

I disagree. Your design is expression based design with following
disadvantages:

a) there is only one possible default message -- "Assertation fault"

b) there is not possibility to show statement for ASSERT ROW_COUNT

c) any static analyse is blocked, because there is not clean semantic

d) In PLpgSQL language a syntax STATEMENT '(' expression ')' is new - there
is nothing yet --- it is discuss from yesterday -- still I am speaking
about plpgsql -- I don't would to refactor plpgsql parser.

e) for your proposal we don't need any special - you can do it as custom
function - then there is no sense to define it. Maximally it can live as
some extension in some shared repository

>
> the idea being that it gets turned into SELECT <expr>; and then
> evaluated.
>
> ASSERT WARNING "data are there" QUERY SELECT ...
>>
>
> I think this could still be parsed correctly, though I'm not 100% sure on
> that:
>
> ASSERT WARNING (EXISTS(SELECT ..)), 'data are there';
>

PLpgSQL uses a ';' or some plpgsql keyword as SQL statement delimiter. It
reason why RETURN QUERY ... ';' So in this case can practical to place SQL
statement on the end of plpgsql statement.

parenthesis are not practical, because it is hard to identify bug ..

A simplicity of integration SQL and PLpgSQL is in using "smart" keywords -
It is more verbose, and it allow to well diagnostics

>
> For extra points the error detail could work similarly to
> print_strict_params. e.g. ASSERT(row_count = 1); would display the value
> of row_count in the DETAIL line, since row_count was a parameter to the
> expression.
>
>
>
> .marko
>