Re: 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