postgres return json from a function
This is nothing to do with json vs other return types. You can't use plain SELECT
in a PL/PgSQL function, it has to be SELECT INTO
, RETURN QUERY SELECT
, or PERFORM
. Per the HINT
error.
In your case all you need is a plain SQL function.
CREATE FUNCTION find_val(val text) RETURNS json AS$$SELECT row_to_json(sample) FROM sample where $1 = ANY(col4);$$ LANGUAGE sql;