Re: Procedure/trigger not working after upgrade from 9.5 to 11.7

IF TG_OP = 'UPDATE' THEN
-- some code involving OLD
ELSE
-- other code

Old is not assigned on inserts.

On Wed, Mar 25, 2020, 1:22 PM Erika Knihti-Van Driessche <
erika(dot)knihti(at)gmail(dot)com> wrote:

> Hi all,
>
> Hope I'm in the correct mail group.. I don't have developer background,
> and after upgrade from 9.5 to 11.7 (RHEL7) the users complain about a
> trigger not working. Here's the error I find in logs:
>
> ERROR: record "old" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
> CONTEXT: SQL statement "SELECT OLD.Ffa_Id Is Null"
> PL/pgSQL function pg_fct_bus_fax_finance_employer() line 20 at IF
>
> Here's part of the procedure (where it already gives the error)
>
> Declare
>
>
>
> Tra_Id Worker.Tra_Id%Type;
>
>
>
> Tra_DateEnregDomTVA Worker.Tra_DateEnregDomTVA%Type;
>
>
>
> Exercice Alloc_compl_vac_worker.Acv_Exercice%Type;
>
>
>
> CodeLangue Worker.Tra_Langue%Type;
>
>
>
> r RECORD;
>
>
>
> r2 RECORD;
>
>
>
> r3 RECORD;
>
>
>
> EREC fax_finance_employer%ROWTYPE;
>
>
>
> Begin
>
>
>
> SET search_path = fs;
>
>
>
>
>
>
>
> If TG_OP = 'INSERT' THEN
>
>
>
> OLD = EREC;
>
>
>
> END IF;
>
>
>
>
>
>
>
>
>
>
> -- if the number is not specified, then it is automatically provided by
> the system
>
> if OLD.Ffa_Id Is Null Then -- Creation
>
>
>
> if NEW.Ffa_NoPayement Is Null Then
>
>
>
> Exercice := EXTRACT(YEAR FROM NEW.FFA_DATEGENERATION);
>
>
>
> if NEW.DAN_ID is not null then
>
>
>
> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
> 'RJA');
>
>
> end if;
>
>
>
> if NEW.DDT_ID is not null then
>
>
>
> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
> 'RDT');
>
>
> end if;
>
>
>
> if NEW.CFS_ID is not null then
>
>
>
> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
> 'RFS');
>
>
> end if;
>
>
>
> if NEW.NDA_ID is not null then
>
>
>
> NEW.Ffa_NoPayement := I_FAX_FINANCE_NumeroNext(Exercice,
> 'AST');
>
>
> end if;
>
>
>
> If NEW.Ffa_NoPayement is null Then
>
>
>
> RAISE EXCEPTION '-20068|There is no more number available...';
>
>
>
> End If;
>
>
>
> End If;
>
>
>
> End IF;
>
>
>
>
> I know it's just a part, but I was hoping someone could immediately say
> AHAA :-)
>
> For the upgrade, I used
> pg_dumpall -g (first only globals)
> psql (import globals)
> pg_dumpall (dump all)
> psql (import all)
>
> No errors received during dump/import.
>
> Thanks for your help & support!
> Erika
>