Hello,
I came across the following problem with integrity constraints and
PL/pgSQL (PostgreSQL version used: 7.4.2):
I defined the following tables, constraints and data:
create table a (n integer);
create table b (n integer);
alter table a add primary key (n);
alter table b add foreign key (n) references a(n);
insert into a values (1);
insert into b values (1);
When trying to execute
delete from a;
this is denied, since the integrity constraint would be violated.
So far, so good.
Now I defined the following function:
create function f () returns void as '
begin
delete from a;
delete from b;
return;
end;
' language plpgsql;
I would expect that
select f();
yields an error message about constraint violation when executing
'delete from a;'.
However, the function is executed without errors, and the tables a and b
are empty after this operation.
It seems that the validity of (integrity) constraints is not checked
inside a function, only after executing a function. Is this a bug or a
feature?
Thanks in advance for your advice,
--
Dr. Christian Rank
Rechenzentrum Universität Passau
Innstr. 33
D-94032 Passau
GERMANY
Tel.: 0851/509-1838
Fax: 0851/509-1802
PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
