Обсуждение: Rules and referential integrity

I seem to have found a way to use the rule system for referential
integrity checks.

The manual says (in the "Rules vs Triggers part) 
"It is possible, to place a qualified rule that rewrites a query to
NOTHING if the value of a column does not appear in another table. But
then the data is silently thrown away and that's not a good idea. If
checks for valid values are required, and in the case of an invalid
value an error message should be generated, it must be done by a
trigger for now." 

Now: why not define a function (in pltcl; could be something else?)create function err(bpchar) returns bpchar as 'error
$1'language 'pltcl'
 

Then, if the value of a column does not appear in another table, you
could rewrite the query to (for instance)     select err('your message')

This will raise a PGRES_FATAL_ERROR , with error message
ERROR:  pltcl: your message

So: it is possible. Why it is better (or worse) than a trigger, I do
not know. Views on that?

> Now: why not define a function (in pltcl; could be something else?)
>
>  create function err(bpchar) returns bpchar
>    as 'error $1' language 'pltcl'
>
> Then, if the value of a column does not appear in another table, you
> could rewrite the query to (for instance)
>       select err('your message')
>
> This will raise a PGRES_FATAL_ERROR , with error message
> ERROR:  pltcl: your message
>
> So: it is possible. Why it is better (or worse) than a trigger, I do
> not know. Views on that?

    Depends  on  the  data  and  usage.  If  only  few tuples are
    affected by a statement, a trigger will usually do  a  better
    job.  Especially  if  the  path  to  find  that tuple is very
    complicated, because the rule has to follow this path another
    time.

    OTOH  if one statement affects thousands of rows, a rule will
    perform better because it is executed in one  internal  extra
    query  instead of invoking the trigger for each single tuple.

    There is no general "this is better than that".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #