postgres return json from a function

Question

I got to know that I can use row_to_json to return json output

For example If my query is:

select * from sample;

I can rewrite it as follows to return json output:

select row_to_json(sample) from sample;

But one thing I am trying to achieve is the same functionality in the function.

To give you an example, here is the function returning table:

CREATE FUNCTION find_val(val text) 
RETURNS SETOF sample AS
$$
BEGIN
RETURN QUERY
SELECT * FROM sample where $1 = ANY(col4);
END;
$$
LANGUAGE 'plpgsql';

Now instead of rows, I want to return JSON output from my function. How can I do that ?

Here is what I have tried so far:

native=> CREATE FUNCTION find_val(val text) 
RETURNS SETOF sample AS
$$
BEGIN
RETURN QUERY
SELECT row_to_json(sample) FROM sample where $1 = ANY(col4) ; 
END;
$$
LANGUAGE 'plpgsql';
CREATE FUNCTION
native=> select find_val('yo');
ERROR:  structure of query does not match function result type
DETAIL:  Returned type json does not match expected type integer in column 1.
CONTEXT:  PL/pgSQL function find_val(text) line 3 at RETURN QUERY
native=> drop function find_val(text);
DROP FUNCTION



native=> CREATE FUNCTION find_val(val text) 
native-> RETURNS json AS
native-> $$
native$> BEGIN
native$> SELECT row_to_json(sample) FROM sample where $1 = ANY(col4);
native$> END;
native$> $$
native-> LANGUAGE 'plpgsql';
CREATE FUNCTION
native=> select find_val('yo');
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function find_val(text) line 3 at SQL statement
native=>

Craig Ringer · Accepted Answer · 2014-06-14 13:29:38Z

25

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;

answered Jun 14, 2014 at 13:29

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mandeep Singh Over a year ago

Thanks Craig.. this worked. I needed to perform a few more operations within my actual function so used plpgsql only. Found the correct way from the official documentation

CREATE OR REPLACE FUNCTION find_val(val text)  RETURNS json AS $$ DECLARE t_row sample%ROWTYPE; BEGIN SELECT * INTO t_row FROM sample where $1 = ANY(col4); RETURN row_to_json(t_row); END; $$              LANGUAGE 'plpgsql';

Collectives™ on Stack Overflow

postgres return json from a function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related