Dotaz: PlPgSql Select into Problém s TimeStamp

[ZAH]

V následující funkce proběhne OK v případě, že ve složeném typu (uvedený dále ) je položka "dateUpdate" null. Po jejím nastavení server vyhodí chybu ERROR" Invalid input syntax for type timestamp.

Select sloupce proběhne OK i s nastavenou hodnotou. Napadá někoho co by mohlo pomoct.

CREATE OR REPLACE FUNCTION accounting.fu_getflags_10(
	indoc ismaster."T_DocumentId")
    RETURNS ismaster."T_DocumentFlags"
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE flags ismaster."T_DocumentFlags";
BEGIN 
 IF EXISTS (SELECT FROM accounting.accdocheader WHERE "docId" = indoc) THEN
  SELECT "docFlags" INTO flags FROM accounting.accdocheader WHERE "docId" = indoc;
  ELSE 
  flags := ismaster.fu_default_document_flags();
  END IF;
  RETURN flags;
END;
$BODY$;

CREATE TYPE ismaster."T_DocumentFlags" AS
(
	"dateNew" ismaster."D_DateNew",
	"dateUpdate" timestamp without time zone,
	"dateOwner" timestamp without time zone,
	"userNew" ismaster."D_IsUserId",
	"userUpdate" ismaster."D_IsUserId",
	"userOwner" ismaster."D_IsUserId",
	"onlyAuthor" boolean,
	"isAccount" boolean,
	"isFinality" boolean,
	"isArch" boolean,
	"noStorno" boolean,
	"noDelete" boolean
);

ZAH

---

---

Odpovědi

[X]

Po jejím nastavení server vyhodí chybu ERROR

A mohl bych to nastaveni(kde a jak) videt?

[ZAH]

Položka , která vyvolá chybu

 ("2023-06-24 13:51:48.405636","2023-06-24 18:58:39.991784",,"zah       ","zah       ",,,,,,,)

Před nastavením hodnoty projde OK.

("2023-06-24 13:50:42.806875",,,"zah       ",,,,,,,,)

Nastavení se provede v trigru pomoci now() po změně řádku.

    .......
    .....
    NEW."docFlags"."dateUpdate" := now();
    return NEW;

[X]

Mas definovano jako:

"dateUpdate" timestamp without time zone

ale, now() vraci timestamp with time zone

?

[ZAH]

Takže, tím now() to nebude, nahradil jsem ho LOCALTIMESTAMP bez výsledku.

Zkusil jsem SELECT INTO nahradit SQL funkcí a ejhle ono to funguje

Nejprve funkce

CREATE OR REPLACE FUNCTION accounting.fu_getflags_10(
	indoc ismaster."T_DocumentId")
    RETURNS ismaster."T_DocumentFlags"
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
SELECT "docFlags" FROM accounting.accdocheader 
     WHERE  "docId" =  indoc ;
$BODY$;

Problémová část kódu po změně (místo INTO přiřazení)

flags := accounting.fu_getflags_10(NEW."docId");
 -- pro vlozeni radku musi hlavicka povolit update
  IF NOT ismaster.fu_docflags_can_update(flags) THEN 
    RETURN null;	
	   END IF;

Čím dál více se přikláním k tomu, že v pgplsql dělám/je nějaká chyba při přiřazování komplexních typů. Objevil se další problém s podobným kódem . Zde vyhodí chybu ERROR" Invalid input syntax for type double precission

DECLARE flags ismaster."T_DocumentFlags";
        debcr ismaster."T_DebitCredit";
begin
.....
.....
 IF  NEW."rowNum" = 1 AND NEW.debitcredit IS null THEN 
  SELECT debitcredit INTO debcr FROM accounting.accdocheader 
      WHERE "docId" = NEW."docId";
	  NEW.debitcredit := ismaster.fu_change_debitcredit(debcr);
 END IF;
.....
.....

Definice typu

CREATE TYPE ismaster."T_DebitCredit" AS
(
	debit ismaster."D_AccountValue",
	credit ismaster."D_AccountValue"
);

[okbobcz]

Můžeš někam hodit nějaký krátký reproducer? Může tam být buga. Update atributu sloupce kompozitního typu je v Postgresu podporovaný relativně krátce. Dřive se bokem musel vytvořit celý kompozit a tím pak přepálit původní kompozitní hodnotu. Mne prace s kompozitem na 16tce funguje bez problemu (a urcite by fungovala i v 15tce pripadne 14tce):

(2023-06-25 23:51:46) postgres=# \d foot
                 Composite type "public.foot"
┌────────┬───────────────────┬───────────┬──────────┬─────────┐
│ Column │       Type        │ Collation │ Nullable │ Default │
╞════════╪═══════════════════╪═══════════╪══════════╪═════════╡
│ a      │ date              │           │          │         │
│ b      │ character varying │           │          │         │
│ c      │ date              │           │          │         │
└────────┴───────────────────┴───────────┴──────────┴─────────┘

(2023-06-25 23:51:57) postgres=# \d xx
                       Table "public.xx"
┌────────┬───────────────────┬───────────┬──────────┬─────────┐
│ Column │       Type        │ Collation │ Nullable │ Default │
╞════════╪═══════════════════╪═══════════╪══════════╪═════════╡
│ id     │ integer           │           │          │         │
│ f      │ foot              │           │          │         │
│ d      │ character varying │           │          │         │
└────────┴───────────────────┴───────────┴──────────┴─────────┘

(2023-06-25 23:52:01) postgres=# do $$
declare x xx;
begin
  x := (1, null, 'ahoj');
  x.f.a := now();
  x.f.b := 'nazdar';
  x.f.c := current_date;
  raise notice '% %', x.f, (x.f).c;
end;
$$;
NOTICE:  (2023-06-25,nazdar,2023-06-25) 2023-06-25
DO

[ZAH]

Včera jsem našel odkaz, který obdobný problém popisuje. Jde skutečně o problém načtení komplexního typu v plpgsql do proměnné. Je rozdíl mezi funkcí plpgsql a sql funkcí.

Obejití problému je poměrně jednoduché načíst řádek tabulky tabulka%ROWTYPE a pak pracovat s načtenou řádkou. Obejití jsou možné i jiné, např. přes text. Odpoledne připojím ještě nalezený odkaz na popis problému. ZAH

[ZAH]

https://stackoverflow.com/questions/70166528/invalid-input-syntax-for-type-integer-2-2-with-composite-data-type-while-ex

[ZAH]

Příloha:

• Err.sql (2455 bytů)

Přikládám export testu databáze s chybou ve funkci tes, Pro její reprodukování mě postačí SELECT public.test().

Vyplyvne chybu

ERROR:  invalid input syntax for type date: "(2023-01-01,Test,2022-11-11)"
CONTEXT:  PL/pgSQL funkce test() řádek 3 na SQL příkaz
SQL stav: 22007

[ZAH]

Tohle je fíčura a ne bug . Na tohle už jsem narazil několikrát a ve své implementaci PL/pgPSM jsem to i řešil a vyřešil. V PL/pgSQL to se to ale změnit (opravit) nedá (kvůli zpětné kompatibilitě), a také aby se nezanášelo víc kódu do jedné relativně krátké, ale nyní už docela komplikované rutiny. I když hlavní problém je zpětná kompatibilita (a ono by to stejně nešlo vyřešit na 100%).

Tam je problém s vícenásobným zanořením. Dobře je to vidět, pokud si výsledek uložíte do proměnné typu record a zobrazíte.

CREATE OR REPLACE FUNCTION public.test()
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  SELECT fx INTO r FROM table1 WHERE id = 5;
  RAISE NOTICE '% %', r, row_to_json(r); 
  RETURN true; 
END;$function$;
CREATE FUNCTION
(2023-06-27 16:11:42) postgres=# SELECT public.test();
NOTICE:  ("(2023-01-01,Test,2022-11-11)") {"fx":{"a":"2023-01-01","b":"Test","c":"2022-11-11"}}
┌──────┐
│ test │
╞══════╡
│ t    │
└──────┘
(1 row)

Jde o to, že výsledkem dotazu je n-tice hodnot, která se uloží do jiné n-tice proměnných nebo kompozitu (což je dnes už něco jiného, ale dříve to byla zase n-tice proměnných) tj (c1,c2,c3) -> (var1,var2,var3), (c1,c2,c3)->(f1,f2,f3). Interně výsledkem každého dotazu je kompozit. Pokud je nějaký sloupec v dotazu kompozit, pak je ten kompozit zanořený, a tudíž vy jej nemůžete přiřadit proměnné kompozitního typu, která nečeká ještě další úroveň. Ve vašem případě se Postgres snaží přiřadit první sloupec výsledku první položce kompozitu - z čehož pak plyne ta zmatečná hláška.

Co můžete udělat?

a) Před přiřazením zdrojový kompozit rozbalit. Pak výsledek bude sedět cíli.

CREATE OR REPLACE FUNCTION public.test()
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
DECLARE ff foot;
BEGIN
  -- SELECT (fx).a, (fx).b, (fx).c INTO ff FROM table WHERE id = 5 -- je taky ok
  SELECT (fx).* INTO ff FROM table1 WHERE id = 5; 
  RETURN true; 
END;$function$;

b) Použít přiřazovací příkaz a ANSI zápis

CREATE OR REPLACE FUNCTION public.test()
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
DECLARE ff foot;
BEGIN
  ff := (SELECT fx FROM table1 WHERE id = 5); 
  RETURN true; 
END;$function$;

Jádro pudla je v designu SQL, které s kompozitními typy vůbec nepočítá (alespoň v těch nejstarších variantách), a ve chvíli, kdy se do tabulek ukládají kompozitní hodnoty, tak některé syntaxe jsou v kolizi. Navíc PL/pgSQL je mix dvou absolutně rozdílných jazyků - SQL a ADA (pro PL/pgSQL hodně redukovaná ADA). Není to jeden jazyk. A občas někde ty švy (kolize) jsou vidět.

[ZAH]

Dík, nějak jsem se k tomu také postupně dobral, ale tys to shrnul překrásně. Není co dodat.

ZAH