From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@lists.postgresql.org
Subject: error in function, works when typed
Date: Wed, 25 Apr 2018 10:06:11 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201804251006.11072.gary.stainburn@ringways.co.uk>
Precedence: bulk

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