From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: error in function, works when typed
Date: Wed, 25 Apr 2018 10:56:11 +0100
User-Agent: KMail/1.9.10
References: <201804251006.11072.gary.stainburn@ringways.co.uk>
 <8483951524648767@web10o.yandex.ru>
In-Reply-To: <8483951524648767@web10o.yandex.ru>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201804251056.11152.gary.stainburn@ringways.co.uk>
Precedence: bulk

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)=20
RETURNS integer as $$
DECLARE
  ROWCOUNT integer;
BEGIN
  select count(sr_id) into ROWCOUNT from service_receptions where sr_id =3D=
=20
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 =3D SRID and sj_state < 90;
  if ROWCOUNT =3D 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 =3D SRID and sj_state < 90;
  update service_jobs set sj_state=3D90=20
      where sj_date < CURRENT_DATE and sj_sr_id =3D SRID and sj_state < 90;
  RETURN ROWCOUNT;
END
$$ LANGUAGE plpgsql;

On Wednesday 25 April 2018 10:32:47 Samed YILDIRIM wrote:
> Hi Garry,
> =C2=A0
> 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.=20
> with sr_update_cte as (update service_jobs set sj_state=3D90
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0where sj_date < CURRENT_DATE and sj_s=
r_id =3D SRID and sj_state < 90
> =C2=A0=C2=A0=C2=A0=C2=A0returning 1) select count(*) into ROWCOUNT from s=
r_update_cte;
> =C2=A0
> Best regards.
> Samed YILDIRIM
> =C2=A0
> =C2=A0
> =C2=A0
> 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 t=
ry
> 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
> =C2=A0=C2=A0ROWCOUNT integer;
> BEGIN
> =C2=A0=C2=A0select count(sr_id) into ROWCOUNT from service_receptions whe=
re sr_id =3D
> SRID;
> =C2=A0=C2=A0IF NOT FOUND THEN
> =C2=A0=C2=A0=C2=A0=C2=A0raise exception 'Reception ID invalid';
> =C2=A0=C2=A0END IF;
> =C2=A0=C2=A0insert into service_jobs_log (sj_id, sj_u_id, sj_text)
> =C2=A0=C2=A0=C2=A0=C2=A0select sj_id, UID,'Job cleared down' from service=
_jobs
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0where sj_date < CURRENT_DATE and sj_s=
r_id =3D SRID and sj_state < 90;
> =C2=A0=C2=A0update service_jobs set sj_state=3D90
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0where sj_date < CURRENT_DATE and sj_s=
r_id =3D SRID and sj_state < 90
> =C2=A0=C2=A0=C2=A0=C2=A0returning ROWCOUNT;
> =C2=A0=C2=A0RETURN ROWCOUNT;
> END
> $$ LANGUAGE plpgsql;
> ----
>
> goole=3D# insert into service_jobs_log (sj_id, sj_u_id, sj_text)
> =C2=A0=C2=A0=C2=A0select sj_id, 25,'Job cleared down' from service_jobs
> =C2=A0=C2=A0=C2=A0where sj_date < CURRENT_DATE and sj_sr_id =3D 10 and sj=
_state < 90;
> INSERT 0 0
> goole=3D# update service_jobs set sj_state=3D90 where sj_date < CURRENT_D=
ATE
> and sj_sr_id =3D 10 and sj_state < 90;
> UPDATE 0
> goole=3D# 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=3D#
> =C2=A0


=2D-=20
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk=20