I’m trying to call a stored function in postgres 12 DB which takes 1 parameter of json type and returns result of json type.
The function is like this:
CREATE OR REPLACE FUNCTION public.get_users( data json, OUT result json) RETURNS json LANGUAGE 'plv8' COST 100 VOLATILE AS $BODY$const dataJson = JSON.parse(data); const arg_id = dataJson.id; const arg_token = dataJson.token; const arg_ids = dataJson.ids.join(","); result = {}; const getAuthUserResult = plv8.execute( 'SELECT id, token, deleted FROM public.users WHERE id = $1', [arg_id]); const authUser = getAuthUserResult[0]; switch (true) { case getAuthUserResult.length === 0: { result.code = "ERR_SENDER_NOTFOUND"; break; } case authUser.token !== arg_token: { result.code = "ERR_SENDER_INVALIDTOKEN"; break; } case authUser.deleted !== 0: { result.code = "ERR_SENDER_DELETED"; break; } default: { result.code = "OK" } } if (result.code === "OK") { result.users = plv8.execute( 'SELECT $1 FROM public.users WHERE id IN ($2)', ["name", arg_id]); } $BODY$; ALTER FUNCTION public.get_users(json) OWNER TO postgres;
The function must take a json with keys: “id” – for id of request sender, “token” – for it’s secret and “targets” – for ids of target users, as follows:
{ "id": 448, "token": "someToken", "targets": [449, 450, 451] }
But when I try calling the function by an SQL query:
SELECT * FROM get_users('{"id":448,"token":"someToken","targets":[449,450,451]}');
I get an error:
ERROR: SyntaxError: Unexpected token o in JSON at position 1 CONTEXT: undefined() LINE 0: [object Object] SQL state: XX000
I have double checked json, and it seems to be valid. Also a lot of resources present this way of calling functions with json typed parameter. What can be wrong with the way I do it?
Advertisement
Answer
The problem was actually in the function code intself.
First:
const dataJson = JSON.parse(data); const arg_id = dataJson.id;
This is invalid. The proper way to access json values is simple:
const arg_id = data.id;
And second:
return result;
is missing at the end.