Skip site navigation (1) Skip section navigation (2)

Re: abnormal behaviour of a CHECK CONSTRAINT with a regular

From: Daniel Struck <struck(dot)d(at)retrovirology(dot)lu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: abnormal behaviour of a CHECK CONSTRAINT with a regular
Date: 2004-02-10 16:08:58
Message-ID: 20040210170858.4cf42b6d.struck.d@retrovirology.lu (view raw or flat)
Thread:
Lists: pgsql-bugs
> I don't see any bug here; it's just that CHECK constraints are applied
> after any BEFORE triggers are run.  Since the triggers could change the
> data to be inserted, the reverse order would be insecure.


Ups, it did make some false assumptions.

Thank you for the clarification.


If I understood now correctly, first the type, then the trigger and last the check constraint is checked.

This makes it also impossible to change the type of a value by a trigger?

Say I would want to change the input of a user to a date, this wouldn't be possible, because in first instance the type is not a date.

Here is an example I tried out:

CREATE TABLE test(datum DATE);

CREATE OR REPLACE FUNCTION function_test()
RETURNS trigger
AS 'BEGIN

new.datum := (''15.06.'' || new.datum)::date;

RETURN new;
END;'
LANGUAGE plpgsql;

CREATE TRIGGER trigger_test
                          BEFORE INSERT OR UPDATE
                          ON test FOR EACH ROW
                          EXECUTE PROCEDURE function_test();

INSERT INTO test VALUES('2003');


Best regards,
Daniel

-- 
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck(dot)d(at)retrovirology(dot)lu

In response to

Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2004-02-10 16:10:13
Subject: Re: abnormal behaviour of a CHECK CONSTRAINT with a regular
Previous:From: Tom LaneDate: 2004-02-10 15:38:40
Subject: Re: abnormal behaviour of a CHECK CONSTRAINT with a regular expression when a trigger is added

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group