Converting the result of a dynamic query to json

Question

I'm trying to convert a dynamic query result into json and then return that json as the result of this function (this is a simplified version, the WHERE clause in my actual code is considerably longer).

CREATE OR REPLACE FUNCTION get_data_as_json(tbl regclass, p_version_id integer)
  RETURNS json AS $$
BEGIN
    RETURN to_json( EXECUTE 'SELECT * FROM '|| tbl
                         || ' WHERE version_id = p_budget_version_id' );
END;
$$ LANGUAGE plpgsql;

However, this code results in a type "execute" does not exist error.
How do I run the dynamic query, and then convert the result to JSON?

check http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql - isn't "select array_to_json(array_agg(row_to_json(t)))" example what you´re looking for? — Leo, Feb 06 '14 at 01:23
That doesn't take the dynamic portion of the question into account (but the "array_to_json(array_agg(row_to_json" might help. Thanks!) — RodeoClown, Feb 06 '14 at 01:28
for the dynamic part, don't you have to use return query? see http://stackoverflow.com/questions/7945932/how-return-result-of-a-select-inside-a-function-in-postgresql — Leo, Feb 06 '14 at 01:34

Craig Ringer · Answer 1 · 2014-02-06T04:28:08.473

3

If you were returning SETOF you'd need to use the RETURN QUERY EXECUTE construct, producing a dynamic query that returns what you want. Since you're not, use regular EXECUTE ... INTO a variable that you then return.

Untested, but in vaguely the right direction:

CREATE OR REPLACE FUNCTION get_data_as_json(tbl regclass, p_version_id integer) RETURNS json AS $$
DECLARE
    my_result json;
BEGIN
    EXECUTE format('SELECT to_json(*) FROM %I WHERE version_id = p_budget_version_id',tbl) INTO my_result;
    RETURN my_result;
END;
$$ LANGUAGE plpgsql;

edited Feb 06 '14 at 04:28

answered Feb 06 '14 at 04:04

Craig Ringer

307,061
76
688
778

Unfortunately that returns me an error of "ERROR: cannot use RETURN QUERY in a non-SETOF function" (I'd tried something similar previously) – RodeoClown Feb 06 '14 at 04:22
@RodeoClown Try amended version above. Again, untested so you might need to fix up any details. – Craig Ringer Feb 06 '14 at 04:28
Thanks Craig. It still doesn't like the `RETURN QUERY` bit, because it's returning JSON, not a SETOF XXX – RodeoClown Feb 06 '14 at 04:38
@RodeoClown There's no `RETURN QUERY` there in the updated version. Look again? – Craig Ringer Feb 06 '14 at 04:49
Ah, sorry about that - the comment must have updated, but not the post for some reason. I'll try it tomorrow morning. Thanks! – RodeoClown Feb 06 '14 at 07:55

score 3 · Answer 2 · edited May 23 '17 at 10:25

What @Craig wrote. But a somewhat different solution with additional fixes:

CREATE OR REPLACE FUNCTION get_data_as_json(tbl regclass
                                          , p_version_id integer
                                          , OUT my_result json) AS
$func$
BEGIN
   EXECUTE format('SELECT to_json(*) FROM %s
                   WHERE  version_id = $1'
                  ,tbl)
   INTO  my_result
   USING p_version_id;
END
$func$ LANGUAGE plpgsql;

Simplest with an OUT parameter. You can directly SELECT INTO it and be done.
When using the object identifier type regclass (which is perfect for the case), the identifier is quoted automatically (if needed) when output as text (automatically). Therefore, use %s with format(), not %I.
I think the parameter p_version_id has been overlooked so far. I suspect a typo in the original code ("p_budget_version_id") combined with invalid syntax. I suggest the USING construct with EXECUTE. Details in this related question:
INSERT with dynamic table name in trigger function

Yeah, there was definitely a typo there. Thanks. – RodeoClown Feb 07 '14 at 00:10 — RodeoClown, Feb 07 '14 at 00:10

Converting the result of a dynamic query to json

2 Answers2