PostgreSQL - SQL Error [42601]: ERROR: query has no destination for result data

Question

I am creating a function to update records in my table and output a json document based on the result, however i am receiving a no destination for result error.

The code that i am using is:

create or replace function update_frequency(_id int,_shipping_method_id int,_day_of_week frequency,_time_range timerange, _maximum_orders int, p_time_range timerange, out out_json json)
  returns json as
$$

begin
 select time_range into p_time_range from "_frequence" where id = _id;
    if not found then 
         raise EXCEPTION '{"error":"frequence id not found"}';
    else 
        if p_time_range = _time_range then
            raise notice '{"error":"they are equal"}';
            WITH upd1 AS (
                update "_frequence" set 
                    id = _id,
                    shipping_method_id = _shipping_method_id,
                    day_of_week = _day_of_week,
                    maximum_orders = _maximum_orders
                where id = _id
                RETURNING id,shipping_method_id, day_of_week, time_range,maximum_orders
            )
            select row_to_json(t) from (
                Select id, shipping_method_id, day_of_week, time_range ,maximum_orders
                FROM upd1 where upd1.id = id
            )t;
            out_json = row_to_json(t);
        elseif (p_time_range && _time_range) then
            raise EXCEPTION '{"error":"they are overlapping"}';
        else
            WITH upd1 AS (
                update "_frequence" set 
                    id = _id,
                    shipping_method_id = _shipping_method_id,
                    day_of_week = _day_of_week,
                    time_range = _time_range,
                    maximum_orders = _maximum_orders
                where id = _id
                RETURNING id,shipping_method_id, day_of_week, time_range,maximum_orders
            )
                select row_to_json(t) from (
                    Select id, shipping_method_id, day_of_week, time_range ,maximum_orders
                    FROM upd1 where upd1.id = id
                )t;
            out_json = row_to_json(t);
        end if;
    end if; 
end;
$$ LANGUAGE plpgsql;

The function call is as follow:

select * from update_frequency(5, 1, 'Tuesday', timerange(time '20:00', time '20:59', '[]'), 4,null);

I am receiving this:

SQL Error [42601]: ERROR: query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL function update_frequency(integer,integer,frequency,timerange,integer,timerange) line 10 at SQL statement

I am not sure what is going wrong and would need somebody's assistance.

Laurenz Albe · Accepted Answer · 2020-11-17 11:41:36Z

1

You have to include the assignment in the query:

WITH (...)
SELECT row_to_json(t) INTO out_json
FROM ...;

answered Nov 17, 2020 at 11:41

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - SQL Error [42601]: ERROR: query has no destination for result data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related