Re: error in function, works when typed - Mailing list 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.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