agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feederror in function, works when typed
3+ messages / 2 participants
[nested] [flat]
* error in function, works when typed
@ 2018-04-25 09:06 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Gary Stainburn @ 2018-04-25 09:06 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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=#
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: error in function, works when typed
@ 2018-04-25 09:32 Samed YILDIRIM <samed@reddoc.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Samed YILDIRIM @ 2018-04-25 09:32 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
<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>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: error in function, works when typed
@ 2018-04-25 09:56 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 3+ messages in thread
From: Gary Stainburn @ 2018-04-25 09:56 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-04-25 09:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-25 09:06 error in function, works when typed Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-04-25 09:32 ` Samed YILDIRIM <samed@reddoc.net>
2018-04-25 09:56 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox