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=#