CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.6
Niv
Niv

Reputation: 281

Use now or current_timestamp inside a postgres function

trying to update a JSONB column in a table using the || operator and insert current time using now or current_timestamp but not able to.

my jsonb_column structure is

{
  "status": "oldStatus",
  "statusUpdatedTimestamp": null //this field might be present or not, if it's present it has to be overwritten. if not, it has to be added
}

create or replace function update_jsonb() 
returns trigger language plpgsql 
as $function$ 
begin 
    if (TG_OP = 'UPDATE' and old.jsonb_column ->> 'status' <> new.jsonb_column ->> 'status') then 
        --need statusUpdatedTimestamp to have the current time
        new.jsonb_column = new.jsonb_column || '{"statusUpdatedTimestamp": ' now ' }';
    end if;

    return new;
end;
$function$ ;

my expected output of jsonb_column is

{
  "status": "newStatus",
  "statusUpdatedTimestamp": '2019-12-11 10:10:35'
}

Upvotes: 1

Views: 883

Answers (1)

klin
klin

Reputation: 121604

Use the function jsonb_build_object(), e.g.:

select jsonb_build_object('statusUpdatedTimestamp', now()::text)

                     jsonb_build_object
-------------------------------------------------------------
 {"statusUpdatedTimestamp": "2019-12-11 19:39:22.950725+01"}
(1 row)

or

select jsonb_build_object('statusUpdatedTimestamp', to_char(now(), 'YYYY-MM-DD HH24-MI-SS'))

                jsonb_build_object
---------------------------------------------------
 {"statusUpdatedTimestamp": "2019-12-11 19:39:52"}
(1 row)

The body of your trigger function may look like this:

if TG_OP = 'UPDATE' and old.jsonb_column ->> 'status' <> new.jsonb_column ->> 'status' then 
    new.jsonb_column = new.jsonb_column || jsonb_build_object('statusUpdatedTimestamp', to_char(now(), 'YYYY-MM-DD HH24-MI-SS'));
end if;
return new;

Upvotes: 2

Related Questions