How to call udf of pg and handle jsonb return value?

[gzliudan]

I defined many functions which return value is jsonb data type in postgresql. How to call pg's udf and handle jsonb return value in pgmoon?

[leafo]

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?

[gzliudan]

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;

[gzliudan]

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.

[leafo]

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)

[gzliudan]

thank you very much.