Thanks 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'.
I attach the text of the entire create function instruction, and also of the 
create table instruction. 
CREATE FUNCTION public.check_account_from_bill_items(id integer) RETURNS 
boolean    LANGUAGE plpgsql    AS $$DECLAREexp varchar;
BEGINselect into exp account from bill_items where account in (select name from 
accounts where type='Expense' or type='Asset' or type='Stock') and 
bill_items.id=id;if exp is not null thenreturn true;elsereturn false;end if;
END;$$;
-------------------------------------------
CREATE TABLE public.accounts (    name character varying NOT NULL,    type 
character varying NOT NULL,    vat real,    account_name character varying,    
account_number character varying,    sorting_code character varying,    
security_code character varying,    website character varying,    email 
character varying,    username character varying,    password character 
varying,    institution character varying,    address text,    telephone 
character varying,    description text,    security_no_for_telephone character 
varying,    id integer NOT NULL,    subtype character varying,    jnbp 
numeric(3,2) DEFAULT 0.5,    currency character(3) DEFAULT 'GBP'::bpchar,    
foreign_currency character(3),    stopped_using date,    vat_purchase boolean 
DEFAULT false,    vat_income boolean DEFAULT false,    balance_sheet_type 
character varying);

ALTER TABLE public.accounts 

Looking at the documentation for Create Function, I'm not entirely sure whether 
there is anything in there which is not supposed to be there. At any rate, the 
system does not seem to allow the creation of functions that are 'illegal' for 
the most part, and I cannot see why anything in here WOULD be illegal. We are 
working with version 10.23, and I cannot send you the entire output of the 
pg_dump file because it is 3.3 GB, but am happy to send you any parts that 
might help. 
Many thanks for you help - any further suggestions would be great!


    On Monday, 18 September 2023 at 23:22:10 BST, Adrian Klaver 
<adrian.kla...@aklaver.com> wrote:  
 
 On 9/18/23 14:46, Johnson, Bruce E - (bjohnson) wrote:

Reply to list also.
Ccing list to return your answer there.

> It turned out that for some reason the perl DBD::Pg module was trying to 
> connect twice, the first failed because of a typo in the password, the 
> second because it tried to connect without ssl. I am not sure why it 
> tried twice.
> 

>> -- 
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
> 
> -- 
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
> 
> Institutions do not have opinions, merely customs
> 
> 

-- 
Adrian Klaver
adrian.kla...@aklaver.com



  

Reply via email to