How to call udf of pg and handle jsonb return value? about pgmoon HOT 5 CLOSED

json data should automatically be decoded to a lua table as long as the type of the value is correct coming from postgres. Are you sure it's not accidentally a string when being returned?

from pgmoon.

below is a udf example in my pg database, I want the return value is json or string data type after call udf in lua:
CREATE OR REPLACE FUNCTION get_my_data () RETURNS JSONB
AS
$BODY$
DECLARE
BEGIN
RETURN jsonb_build_object('return_code', 0, 'result', '{"id":"1","name":"test1"}'::JSONB);
END;
$BODY$
LANGUAGE plpgsql;

from pgmoon.

Automatically decoded json to lua table is acceptable. But it's better to provide 2 function and let user choose return type is string or lua table.

from pgmoon.

You can cast the value to a string from postgres using ::text

If you want to disable json deserialization on the lua end, you can run:

client:set_type_oid(114, nil)
client:set_type_oid(3802, nil)

from pgmoon.

thank you very much.

from pgmoon.