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:
x
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.