Re: proposal: plpgsql - Assert statement

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.

> 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));

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';

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