Re: Foreign key behavior different in a function and outside

Thanks for the response Tom. I am running postgres 8.3.7.

Yes, his is a highly simplified version, but I also didn't get the column name right. One more attempt at that.
CREATE OR REPLACE FUNCTION delete_B(id integer)
RETURNS void AS
$BODY$
declare
vSql varchar;
BEGIN
delete from B where B_id = id;

END;

The core issue is the foreign key reference being deleted even though there is no cascade delete defined.
Thanks again.

-mridula

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, March 04, 2010 7:45 PM
To: Mridula Mahadevan
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Foreign key behavior different in a function and outside

Mridula Mahadevan <mmahadevan(at)stratify(dot)com> writes:
> CREATE OR REPLACE FUNCTION delete_B(id integer)
> RETURNS void AS
> $BODY$
> declare
> vSql varchar;
> BEGIN
> delete from B where id = id;

> END;

That's a really dangerous function definition --- the system is not by
any means bright enough to figure out that you'd like one instance of
"id" to refer to B's column and the other instance to refer to the
function parameter. It's going to resolve both the same way (both as
the function parameter, as it happens); meaning that what you actually
have here is "delete from B where true".

I'm not sure how that ties into your claimed issue with foreign keys,
and maybe the above is just a hastily oversimplified version of what
you really did. But we aren't going to be able to figure out the
problem without an exact example.

FWIW, I seem to remember that really old versions of Postgres used to
have some issues with the timing of foreign key checks for updates
issued inside functions. That's probably not relevant, but since
you also failed to mention what Postgres version you're dealing with,
it's hard to be sure.

regards, tom lane