Convert query in stored procedure to JSON in POSTGRES

Question

Convert query in stored procedure to JSON in POSTGRES

Navigation

#1 by (2 votes)

1

Hello, I am doing a procedure stored in POSTGRES of type GET. I want to consult the information from this one but it sends me the following error:

CREATE OR REPLACE FUNCTION p.products(_p_id TEXT)
RETURNS TABLE (
_name TEXT
, _total MONEY
, _products SMALLINT) AS $$
BEGIN
RETURN QUERY SELECT row_to_json(t) FROM (SELECT id,
(
    SELECT array_to_json(array_agg(row_to_json(d)))
    FROM(
            SELECT name
            , total
            , products
        ) d
) as productss
FROM a.products
WHERE CAST(p_id as TEXT) like _p_id
)t;
END;
$$ LANGUAGE PLPGSQL;

The error he gives me is this:

structure of query does not match function result type

sql json postgresql

asked by Checo 21.11.2017 в 22:57

source

1 answer

How can I use Postgres INTERVAL with the value of a table column? I want to save and recover the selected value in a Spinner in SharedPreferences

score 2 · Answer 1

The error is indicating the problem. Your query returns records with a column of type JSON . But the clause RETURNS contradicts this when announcing that what is going to return the function is the following:

TABLE (
_name TEXT
, _total MONEY
, _products SMALLINT)

Obviously, that does not fit. The function does not return records with those 3 columns.

To correct the error, modify the clause RETURNS so that it correctly announces the fact that the function will return records with a column of type JSON :

CREATE OR REPLACE FUNCTION p.products(_p_id TEXT)
RETURNS TABLE(_row JSON) AS $$
BEGIN
...