Question 410868 in PostgreSQL Question posted in
The official documentation can be found here.

How to use Postgresql json_set in conjuction with pgsql-http

I have a database trigger function like this:

CREATE OR REPLACE FUNCTION public.webhook()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        PERFORM status FROM 
http_post('https://example.com/receiver', jsonb_set(jsonb_build_object('action',TG_OP,'table',TG_TABLE_NAME),'{value}',
to_jsonb(NEW.*)));
        RETURN NULL;
    END;
$function$
;

But I get now new field "value" inside my json. What is wrong with my function?

What I’m trying to achieve is to get

{
"table": "issues",
"action": "UPDATE",
"value": ####
}

in my request body, with #### containing the changed record.
It works with

PERFORM status FROM 
http_post('https://example.com/receiver', to_jsonb(NEW.*));

but I want to have the action and table too.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    The problem is less inside json_set, as I assumed in the question, it is more in the conjunction with http_post() from pgsql-http. If I cast the jsonb object to text and change that http_post() call to this:

    CREATE OR REPLACE FUNCTION public.webhook()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        PERFORM status FROM 
        http_post('https://automate.nrkt.enerkite.com/webhook-test/redmine', 
            jsonb_build_object( 'action',TG_OP,
                                'table',TG_TABLE_NAME,
                                'value',to_jsonb(NEW.*))::text,
                                'application/json');
        RETURN NULL;
    END;
$function$
;

    it works. Thank You all for helping. Zegarek, you pointed me in the right direction by You debug mechanism, which showed, taht my problem was not where I thought.


  2. 0

    You can get the required structure with something like this:

    CREATE OR REPLACE FUNCTION public.webhook()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        PERFORM status FROM 
        http_post('https://example.com/receiver', 
            jsonb_build_object(
                'action', TG_OP,
                'table', TG_TABLE_NAME,
                'value', to_jsonb(NEW.*)
            )
        );
        RETURN NULL;
    END;
$function$
;

    jsonb_build_object constructs the entire JSON object in one go.

    Login or Signup to reply.
  3. 0

    What you’re showing works ok if you’re trying to nest the new record under the value key:
    demo at db<>fiddle

    CREATE OR REPLACE FUNCTION public.webhook()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        create table debug_ as 
        select 'https://example.com/receiver'
              , jsonb_set(jsonb_build_object( 'action',TG_OP
                                             ,'table',TG_TABLE_NAME)
                          ,'{value}',to_jsonb(NEW.*));
        RETURN NULL;
    END;
$function$
;
create table b(i int default 5);
create trigger trg before insert on b
for each row execute function webhook();
insert into b select;
table debug_;
    ?column? jsonb_set
    https://example.com/receiver {"table": "b", "value": {"i": 5}, "action": "INSERT"}

    If you’re trying to get a JSON version of that record, with table and action added into it, flip the arguments: don’t add the record to a JSON with the pair of keys but instead use jsonb_set() or jsonb_insert() twice to add them to the record.

    Or, concatenate || these together (that’s a dedicated operator for merging jsonb‘s, not to be confused with text concatenation):
    demo at db<>fiddle

    CREATE OR REPLACE FUNCTION public.webhook()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        create table debug_ as 
        select 'https://example.com/receiver'
              , jsonb_build_object( 'action',TG_OP
                                   ,'table',TG_TABLE_NAME)
                || to_jsonb(NEW) as payload;
        RETURN NULL;
    END;
$function$
;
create table b(i int default 5);
create trigger trg before insert on b
for each row execute function webhook();
insert into b select;
table debug_;
    ?column? payload
    https://example.com/receiver {"i": 5, "table": "b", "action": "INSERT"}

    You should be able to to_jsonb(NEW) without unpacking NEW.*.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search