For all of these conversions but one, PL/Java must do time zone computations, with the one exception being, unintuitively, timestamp with time zone. Those classes have never been a good representation for PostgreSQL date/time values, because they are based on, which implies knowledge of a time zone, even when they are used to represent PostgreSQL values with no time zone at all. The JDBC getObject and readObject methods that do not take a Class parameter will return objects of those types when retrieving PostgreSQL date or time values. PL/Java function parameters and returns can be declared in Java to have those types, objects of those types can be passed to tObject, ResultSet.updateObject, and SQLOutput.writeObject methods, as well as to the methods that are specific to those types. The first mappings to be specified in JDBC used the JDBC-specific classes, , and, all of which are based on (but only as an implementation detail they should always be treated as their own types and not as instances of ). To put it another way: " It's not a bug, it's a feature.Mapping between PostgreSQL and Java date/time types Legacy JDBC mappings While you may say now "I always want the timestamp updated.", in my experience it's a questionable choice to paint yourself into a corner like that. Possibly also worth noting is that requiring yourself to update the data/timestamp pairs via the procedural code allows you to do out-of-band updates to data in the table that don't futz the timestamp without disabling the trigger. ![]() ![]() ![]() How will you detect no update has happened and insert as needed? upsert logic - the proc as given only updates values.If you supply multiple payloads at the same time what do you want to happen. multiple payload - I only tested a single valid payload.malformed payload - no id supplied, a supplied multiple times, invalid data supplied, etc.It's worth noting that this approach as-written here likely has a bunch of edge case bugs you'd want to QA for. The proc demonstrates only that you can infer which timestamps need to be updated. Updates that occur outside usage of the procedure with a valid jsonb payload will still require that you update the paired timestamp "manually". Are updated to the same value as they were before.This is true even if specified attributes This will demonstrate to you that attributes which are specified in the json payload are updated alongside their paired timestamps while unspecified attributes are not updated. create procedure bar (payload jsonb)Ĭase when p ? 'a' then (p->'a')::int else f.a end as a,Ĭase when p ? 'b' then (p->'b')::int else f.b end as b,Ĭase when p ? 'c' then (p->'c')::int else f.c end as c,Ĭase when p ? 'a' then now()::timestamptz else f.a_tstz end as a_tstz,Ĭase when p ? 'b' then now()::timestamptz else f.b_tstz end as b_tstz,Ĭase when p ? 'c' then now()::timestamptz else f.c_tstz end as c_tstz create table foo (Ī_tstz timestamptz not null default now(),ī_tstz timestamptz not null default now(),Ĭ_tstz timestamptz not null default now() ![]() Let the business logic do what it is told and if it updates value " a", then you as the administrator require that it also update " a_timestamp" part and parcel of the same transaction.īy way of example: if you're limited in your ability to modify the app code but you can require a json payload, you can use a model like the following ( full dbfiddle here). Trying to outguess the application in terms of whether it really meant to update a certain value or not is a losing game. For my preference, a trigger is the wrong tool for the job here.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |