pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUse of RETURN in pl/pgsql function
5+ messages / 3 participants
[nested] [flat]
* Use of RETURN in pl/pgsql function
@ 2001-02-07 18:20 Jeff Eckermann <jeckermann@verio.net>
0 siblings, 2 replies; 5+ messages in thread
From: Jeff Eckermann @ 2001-02-07 18:20 UTC (permalink / raw)
To: pgsql-sql
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();
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of RETURN in pl/pgsql function
@ 2001-02-07 18:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Eckermann <jeckermann@verio.net>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-02-07 18:47 UTC (permalink / raw)
To: Jeff Eckermann <jeckermann@verio.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Use of RETURN in pl/pgsql function
@ 2001-02-07 20:16 Jeff Eckermann <jeckermann@verio.net>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Eckermann @ 2001-02-07 20:16 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 | 50431
Quarterly | 7742
Semi-Annual | 167
Yearly | 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of RETURN in pl/pgsql function
@ 2001-02-07 20:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Eckermann <jeckermann@verio.net>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-02-07 20:37 UTC (permalink / raw)
To: Jeff Eckermann <jeckermann@verio.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Use of RETURN in pl/pgsql function
@ 2001-02-07 22:32 Jie Liang <jliang@ipinc.com>
parent: Jeff Eckermann <jeckermann@verio.net>
1 sibling, 0 replies; 5+ messages in thread
From: Jie Liang @ 2001-02-07 22:32 UTC (permalink / raw)
To: Jeff Eckermann <jeckermann@verio.net>; +Cc: pgsql-sql
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();
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-02-07 22:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-07 18:20 Use of RETURN in pl/pgsql function Jeff Eckermann <jeckermann@verio.net>
2001-02-07 18:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-07 22:32 ` Jie Liang <jliang@ipinc.com>
2001-02-07 20:16 RE: Use of RETURN in pl/pgsql function Jeff Eckermann <jeckermann@verio.net>
2001-02-07 20:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox