From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: error in function, works when typed |
Date: | 2018-04-25 09:56:11 |
Message-ID: | 201804251056.11152.gary.stainburn@ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(dot)stainburn(at)ringways(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Emi | 2018-04-27 14:39:54 | psql method (initcap and also keep Roman number capitalized) |
Previous Message | Samed YILDIRIM | 2018-04-25 09:32:47 | Re: error in function, works when typed |