agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org
Subject: error in function, works when typed
Date: Wed, 25 Apr 2018 10:06:11 +0100
Message-ID: <201804251006.11072.gary.stainburn@ringways.co.uk> (raw)

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

view thread (3+ messages)  latest in thread

Message-ID: <201804251006.11072.gary.stainburn@ringways.co.uk>
Permalink:  ../201804251006.11072.gary.stainburn@ringways.co.uk/
Also on:    postgresql.org/message-id/201804251006.11072.gary.stainburn@ringways.co.uk

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
  Subject: Re: error in function, works when typed
  In-Reply-To: <201804251006.11072.gary.stainburn@ringways.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox