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: 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: But

Calling stored function in postgres 12 with json type argument by SQL query from pgAdmin results in error

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?

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.

Advertisement

Answer