<div>Hi Garry,</div><div> </div><div>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.</div><div> </div><div>with sr_update_cte as (update service_jobs set sj_state=90<br /> where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90<br /> returning 1) select count(*) into ROWCOUNT from sr_update_cte;</div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>25.04.2018, 12:06, "Gary Stainburn" <gary(dot)stainburn(at)ringways(dot)co(dot)uk>:</div><blockquote type="cite"><p>I am writing a function to clear down old jobs.<br /><br />As you can see below, the commands work when I type them in, but when I try to<br />use them in a function, the insert fails.<br /><br />Anyone got an idea why? The error suggests that the select does not have a<br />destination, but it feeds the insert.<br /><br />----<br />create or replace function service_cleardown(SRID integer, UID integer)<br />RETURNS integer as $$<br />DECLARE<br /> ROWCOUNT integer;<br />BEGIN<br /> select count(sr_id) into ROWCOUNT from service_receptions where sr_id =<br />SRID;<br /> IF NOT FOUND THEN<br /> raise exception 'Reception ID invalid';<br /> END IF;<br /> insert into service_jobs_log (sj_id, sj_u_id, sj_text)<br /> select sj_id, UID,'Job cleared down' from service_jobs<br /> where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90;<br /> update service_jobs set sj_state=90<br /> where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90<br /> returning ROWCOUNT;<br /> RETURN ROWCOUNT;<br />END<br />$$ LANGUAGE plpgsql;<br />----<br /><br />goole=# insert into service_jobs_log (sj_id, sj_u_id, sj_text)<br /> select sj_id, 25,'Job cleared down' from service_jobs<br /> where sj_date < CURRENT_DATE and sj_sr_id = 10 and sj_state < 90;<br />INSERT 0 0<br />goole=# update service_jobs set sj_state=90 where sj_date < CURRENT_DATE and<br />sj_sr_id = 10 and sj_state < 90;<br />UPDATE 0<br />goole=# select service_cleardown(10,25);<br />ERROR: query has no destination for result data<br />CONTEXT: PL/pgSQL function "service_cleardown" line 11 at SQL statement<br />goole=#<br /> </p></blockquote>