On 9/20/23 10:59 AM, Harry Green wrote:
Thank you Laurenz for your answer,
I have done a complete pg_dump and complete restore, and tried all forms of complete pg_dumps and restores (i.e. the different formats). There is no partial or tables only or data only dumping or restoring. Further, you refer to a 'check constraint' but this is just a function that we wrote with the 'create function...' command, whose name if memory serves correctly happens to begin by 'check...' because that happens to be the most descriptive word for what it does.
The pg_restore is actually correctly recreating the function using the 'create function...' command, but it is doing so on line 95, ahead of the 'create table ... accounts ...' which appears on line 510. The trouble is that in the function itself, we refer to the accounts table, and if I am not mistaken, the whole point of pl/pgsql is that you can refer to tables, etc. so I don't think I have done anything 'illegal'.
The issue is not that a table name is being used in a function, it is that the function is
being used before the table is created.
My suspicion still is that it is being used as a CHECK function.
I would suggest taking a schema only dump of the database e.g:
pg_dump -d <db_name> -s -f db_name_schema.sql
This will dump only the schema(object) definitions. Then search that file for
check_account_from_bill_items. I'm betting it is used somewhere before the accounts table
is created.