agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Re: error in function, works when typed
Date: Wed, 25 Apr 2018 12:32:47 +0300
Message-ID: <8483951524648767@web10o.yandex.ru> (raw)
In-Reply-To: <201804251006.11072.gary.stainburn@ringways.co.uk>
References: <201804251006.11072.gary.stainburn@ringways.co.uk>
<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.stainburn@ringways.co.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>
view thread (3+ messages) latest in thread
Message-ID: <8483951524648767@web10o.yandex.ru>
Permalink: ../8483951524648767@web10o.yandex.ru/
Also on: postgresql.org/message-id/8483951524648767@web10o.yandex.ru
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: samed@reddoc.net, gary.stainburn@ringways.co.uk, pgsql-sql@lists.postgresql.org
Subject: Re: error in function, works when typed
In-Reply-To: <8483951524648767@web10o.yandex.ru>
* 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