referential integrity constraints not checked inside PL/pgSQL functions? - Mailing list pgsql-general

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