Re: proposal: plpgsql - Assert statement - Mailing list pgsql-hackers

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