Thread: Use of RETURN in pl/pgsql function
My script is below.
I thought (based on recent posts) that this use of RETURN is allowed, but
when trying an insert to report_table, I get the following error:
ERROR: control reaches end of trigger procedure without RETURN
I have solved several problems in getting to this point, but have now run
out of ideas. I would appreciate any pointers.
jeffe@kiyoko=> uname -a
FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27
10:44:07 CDT 2000
jeffe@kiyoko=> psql -V
psql (PostgreSQL) 7.0.0
Script:
drop function mrr();
create function mrr() returns opaque as '
begin
if NEW.billing_frequency = ''Monthly'' -- That's doubled single
quotes (and below as well)
then
return NEW;
else
if NEW.billing_frequency = ''Yearly''
then
NEW.rate := NEW.rate/12; NEW.rate_override := NEW.rate_override/12;
return NEW;
else
if NEW.billing_frequency = ''Semi-Annual''
then
NEW.rate := NEW.rate/6; NEW.rate_override := NEW.rate_override/6;
return NEW;
else
if NEW.billing_frequency = ''Quarterly''
then
NEW.rate := NEW.rate/3;
NEW.rate_override := NEW.rate_override/3;
return NEW;
end if;
end if;
end if;
end if;
end;
'language 'plpgsql';
drop trigger mrr_set_trigger on report_table;
create trigger mrr_set_trigger
before insert on report_table
for each row execute procedure mrr();
Jeff Eckermann <jeckermann@verio.net> writes:
> I thought (based on recent posts) that this use of RETURN is allowed, but
> when trying an insert to report_table, I get the following error:
> ERROR: control reaches end of trigger procedure without RETURN
Looks to me like you didn't cover the case where billing_frequency is
not any of the values you tested for. Maybe you just want to raise
an error in that case...
regards, tom lane
I'll be hornswoggled: that works. I hadn't expected a problem, simply because I knew the range in advance: here is the distribution for the select that I was working with: extracts=# select billing_frequency, count (*) from report_table group by billing_frequency;billing_frequency | count -------------------+-------Monthly | 50431Quarterly | 7742Semi-Annual | 167Yearly | 8573 (4 rows) Nevertheless, just adding an "...else return new;" did the trick. I don't understand the logic of this, but then, if I stopped to consider every mystery, I'd never get any work done:-). Thanks very much for your help (again). > -----Original Message----- > From: Tom Lane [SMTP:tgl@sss.pgh.pa.us] > Sent: Wednesday, February 07, 2001 12:48 PM > To: Jeff Eckermann > Cc: 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] Use of RETURN in pl/pgsql function > > Jeff Eckermann <jeckermann@verio.net> writes: > > I thought (based on recent posts) that this use of RETURN is allowed, > but > > when trying an insert to report_table, I get the following error: > > ERROR: control reaches end of trigger procedure without RETURN > > Looks to me like you didn't cover the case where billing_frequency is > not any of the values you tested for. Maybe you just want to raise > an error in that case... > > regards, tom lane
Jeff Eckermann <jeckermann@verio.net> writes:
> I'll be hornswoggled: that works.
> I hadn't expected a problem, simply because I knew the range in advance:
Odd. Maybe there is a compile-time, rather than run-time, check in
plpgsql for falling off the end without a RETURN? I haven't looked ...
regards, tom lane
You may try like: if block end if; return somefakething; no matter this return can be reached or not. then compile will be no problem. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com On Wed, 7 Feb 2001, Jeff Eckermann wrote: > My script is below. > > I thought (based on recent posts) that this use of RETURN is allowed, but > when trying an insert to report_table, I get the following error: > > ERROR: control reaches end of trigger procedure without RETURN > > I have solved several problems in getting to this point, but have now run > out of ideas. I would appreciate any pointers. > > jeffe@kiyoko=> uname -a > FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 > 10:44:07 CDT 2000 > jeffe@kiyoko=> psql -V > psql (PostgreSQL) 7.0.0 > > Script: > > drop function mrr(); > > create function mrr() returns opaque as ' > > begin > > if NEW.billing_frequency = ''Monthly'' -- That's doubled single > quotes (and below as well) > > then > > return NEW; > > else > > if NEW.billing_frequency = ''Yearly'' > > then > > NEW.rate := NEW.rate/12; > > NEW.rate_override := NEW.rate_override/12; > > return NEW; > > else > > if NEW.billing_frequency = ''Semi-Annual'' > > then > > NEW.rate := NEW.rate/6; > > NEW.rate_override := NEW.rate_override/6; > > return NEW; > > else > > if NEW.billing_frequency = ''Quarterly'' > > then > > NEW.rate := NEW.rate/3; > > NEW.rate_override := NEW.rate_override/3; > > return NEW; > > end if; > > end if; > > end if; > > end if; > > end; > > 'language 'plpgsql'; > > drop trigger mrr_set_trigger on report_table; > > create trigger mrr_set_trigger > > before insert on report_table > > for each row execute procedure mrr(); >