On Tue, 10 Feb 2004, Daniel Struck wrote:

Wrong order. Before triggers happen before the table check constraint is
checked (since the triggers can change the data). IIRC, you can currently
have a constraint that is checked before that currently by making the
column type be a domain that has the constraint since the value gets
coerced to the column type (and checked) when making the row value for the
trigger.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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 th=
e 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 :=3D (''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

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

I had just an example, where it would have been convenient, if one could ch=
ange the type.

Let's say you want to give the users the possibility to enter incomplete da=
tes (year, year&month or the complete date). Indeed you could not insert th=
is information as it is. But later on for stats you would like to be able t=
o treat the column as a date.

I have implemented it by storing the user information in a varchar column a=
nd inserting the whole date in another column.
(if the user only provides the year, the date is inserted as 16.05.year,...=
).


Kind regards,

Daniel Struck

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match