Hi Samed,
Thank you for getting back to me. Unfortunately, that wouldn't compile.
Instead I've done it as a separate statement and it works.
Gary
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;
select count(sj_id) into ROWCOUNT from service_jobs
where sj_date < CURRENT_DATE and sj_sr_id = SRID and sj_state < 90;
if ROWCOUNT = 0 THEN
return 0;
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;
RETURN ROWCOUNT;
END
$$ LANGUAGE plpgsql;
On Wednesday 25 April 2018 10:32:47 Samed YILDIRIM wrote:
> 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" <gary.stainburn@ringways.co.uk>:
> 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=#
>
--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk