On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> I am still getting ERROR: invalid input syntax for integer:"42P01" when I am insert a data into the table.
Experience tells me "42P01" this is an SQL Error Code value. The fact
that the value doesn't appear in your input data means that something
in the function is generating that value as opposed to it coming from
the input. Looking up that value in Appendix A informs us that it
means: 42P01 undefined_table. There is insufficient data supplied to
pinpoint your overall problem more closely but the error of attempting
to assign this specific text literal to an integer is easy to find.
> declare
> v_ErrorCode bigint;
You assumed error codes were integers; you were wrong.
> if NEW.mig_seg is null then
> NEW.mig_seq:=v_seq;
> enf if;
This is a straight-up syntax error if the code ever got this far so
probably your runtime error is before this (unless you are not
providing exact code)
> if NEW.mig_filename is null then
> NEW.mig_filename :='Unknown';
> end if;
> exception
> when others then
> v_ErrorCode := SQLSTATE'
Again, not even sure how this executes as written...but this is
apparently the actual assignment location throwing the exception
> even I take the "E" values out the error is still occurred
That just indicates you still need to learn what the E'' string stuff
is all about.
> but if I dropped the function and trigger, the insert is fine
Which means the trigger function has issues since a trigger is just a
catalog entry
David J.