> I forgot to add, this is of course a "simplistic" approach which:
> 1. may be simply wrong
> 2. assumes data is available to user in nformation_schema (I guess the
> information schema lists only data owned by user; yet I am not sure
> about that).
> 3. assumes foreign keys have really simple set up (no FKs depending on
> FKs depending on FKs...)
> In other words, best approach would be "know thy schema" in the
> application. ;)))
Tom and Dawid,
I have 60 foreign key tables with ON DELETE RESTRICT constraint.
I have primary key column in all of my tables.
I want that in case of deletion error immediate parent table name and one
primary key which prevents deletion is displayed to user. So user can
correct bad invoice manually, try next deletion, proceed with next stopper
invoice and so on ...
My application runs as postgres super-user, so it can access all data from
information schema.
So it seems that it is reasonable to create generic routine for this.
Unfortunately I do'nt have enough experience to create such kind of routine
even if using sample SELECT statements written by Dawid.
I have seen similar question asked several times in this newsgroup. This
seems to be a common requirement.
Isn't there really some ready made generic stored procedure which I can use
?
If delete error occurs, Postgres knows the ctid of the restricting record.
Why this ctid cannot be retrieved by ODBC client ?
Andrus.