I have defined a function in my PostgreSQL 10.18 database like this:

CREATE OR REPLACE FUNCTION public.log_event(event json)
RETURNS void
LANGUAGE 'plpgsql' AS
$BODY$
BEGIN
    INSERT INTO "eventlog" VALUES(event::json->'event_type',
                                  event::json->'message',
                                  event::json->'user',
                                  now());
END
$BODY$;

I can call it successfully from pgAdmin like this:

select log_event('{"event_type": "foo", "message": "bar", "user": "foobar"}'::json);

But when I try to call it from my Python code, that talks to the database via the PostGREST API:

event = {
  "event": {
    "event_type": "foo",
    "message": "bar",
    "user": "foobar"
  }
}

requests.post('https://server/rpc/log_event', json=event)

It fails with:

{"hint":"No function matches the given name and argument types. You might need to add explicit type casts.",
"details":null,
"code":"42883",
"message":"function public.log_event(event => text) does not exist"}

What am I doing wrong?

CodePudding user response：

You declared the function parameter as type json. Your function call from pgAdmin passes type json - you have an explicit cast there (::json).

It would also work with an untyped string literal.

But your call from Python passes type text, and that's where Postgres bails out. There is no implicit cast registered for text --> json. Pass type json or an untyped string literal instead.

(Alternatively, create a function taking text instead, and use an explicit cast (once) inside the function, but that's an inferior solution.)

The PostgREST manual:

You can also call a function that takes a single parameter of type JSON by sending the header Prefer: params=single-object with your request. That way the JSON request body will be used as the single argument.

Bold emphasis mine.

Also, to match your function, there is no outer "event" key in the passed JSON object:

event = {
    "event_type": "foo",
    "message": "bar",
    "user": "foobar"
}

Cleaner function

Aside, your function cleaned up:

CREATE OR REPLACE FUNCTION public.log_event(event json)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO public.eventlog    -- (a, b, c, d) -- ①
   VALUES (event -> 'event_type'  -- ②
         , event -> 'message'     -- ? ③
         , event -> 'user'
         , now());
END
$func$;

① Better provide an explicit target column list (with actual target column names). Else, later schema changes may silently break your code.

② Drop the added cast, event is already type json.

③ You want to store json values? I suspect you really want event ->> 'message' to get text instead. With the ->> operator.