Re: error in function, works when typed

Hi Garry,

It is related with your update line in the function. Update line is finishing with returning but it does not have any target for the output. You can use cte to achieve this. Update line should be like following.

with sr_update_cte as (update service_jobs set sj_state=90
where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90
returning 1) select count(*) into ROWCOUNT from sr_update_cte;

Best regards.

Samed YILDIRIM

25.04.2018, 12:06, "Gary Stainburn" <[email protected]>:

I am writing a function to clear down old jobs.

As you can see below, the commands work when I type them in, but when I try to
use them in a function, the insert fails.

Anyone got an idea why? The error suggests that the select does not have a
destination, but it feeds the insert.

----
create or replace function service_cleardown(SRID integer, UID integer)
RETURNS integer as $$
DECLARE
ROWCOUNT integer;
BEGIN
select count(sr_id) into ROWCOUNT from service_receptions where sr_id =
SRID;
IF NOT FOUND THEN
raise exception 'Reception ID invalid';
END IF;
insert into service_jobs_log (sj_id, sj_u_id, sj_text)
select sj_id, UID,'Job cleared down' from service_jobs
where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90;
update service_jobs set sj_state=90
where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90
returning ROWCOUNT;
RETURN ROWCOUNT;
END
$$ LANGUAGE plpgsql;
----

goole=# insert into service_jobs_log (sj_id, sj_u_id, sj_text)
select sj_id, 25,'Job cleared down' from service_jobs
where sj_date < CURRENT_DATE and sj_sr_id = 10 and sj_state < 90;
INSERT 0 0
goole=# update service_jobs set sj_state=90 where sj_date < CURRENT_DATE and
sj_sr_id = 10 and sj_state < 90;
UPDATE 0
goole=# select service_cleardown(10,25);
ERROR: query has no destination for result data
CONTEXT: PL/pgSQL function "service_cleardown" line 11 at SQL statement
goole=#