0

I have a table fepu00 and trigger on it. The part of code causing tha problem looks like follows:

        if (old_record.potime = NEW.putime --new AP (read in statement above)
            or old_record.pupisb::NUMERIC != NEW.pupisb::NUMERIC) then
        insert into dl356_table
        values (old_record.poid, old_record.poidma, old_record.ponmaf,
                old_record.adstr, old_record.adpsc, old_record.adcit, old_record.adidze,
                NEW.pupisb::numeric,
                case when old_record.potime = NEW.putime then '1' else '2' end,
                NEW.putime);
    end if;

The query I'm executing is really simple:

update fepu00 set pudas = ? where puid = ?

It works, but only for some quantity of times. Then it throws:

ERROR: type of parameter 25 (numeric) does not match that when preparing the plan (text)
Where: PL/pgSQL function dl356_trigger() line 75 at IF

Number of updated records varies from a few to a few hundreds. When I run the same query (with the same parameters) again, it works properly until the next fail.

Thanks for any suggestions.

agad
  • 2,192
  • 1
  • 20
  • 32
  • 1) Add the complete error message to question. 2) What there is of the error is pretty clear a `numeric` value is being provided where a `text` one is expected. In `psql` do `\ef dl356_trigger 75` to get to the exact line it is complaining about. 3) The `...preparing the plan ...` section indicates you may need to use [Dynamic SQL] to have the plan not be cached. I would try that as last option. – Adrian Klaver Dec 09 '22 at 16:24
  • Please, if possible, put complete SQL code for trigger function and SQL code for creating trigger. Otherwise, it is difficult for us to help. – Ramin Faracov Dec 10 '22 at 15:00
  • @AdrianKlaver 1) Complete error message is `ERROR: type of parameter 25 (text) does not match that when preparing the plan (numeric) Where: PL/pgSQL function dl356_trigger() line 75 at IF` 2) Errror message is pretty clear, but if the type of parameter would bewrong, it would never work. is it possible to get the line number with sql (I use DataGrip).Thanks – agad Dec 12 '22 at 07:45
  • If you can reach the database with DataGrip you can reach it with `psql` so do as I suggested in 2) in my comment. Obviously at some point you are not using the same type of parameter. That is why I suggested [Dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN). It will use a different plan for each run of the function. Read the link for more information. If it work using dynamic sql then you can be pretty sure that the type is changing over the updates. – Adrian Klaver Dec 12 '22 at 21:47

0 Answers0