Thread: function to operate on same fields, different records?
is this kind of thing possible---?
select gpa(student) from student where id=7121;
select gpa(course) from course where id=29931;
select gpa(prof) from prof where id=1321;
i've got several tables each of which have
create table <various-and-sundry> (
...
a int4,
b int4,
c int4,
d int4,
f int4,
...
);
since i'd like to AVOID this nonsense--
create view courseGPA as
select *,
(a * 4 + b * 3 + c * 2 + d)
/
(a + b + c + d + f) as gpa
from course;
create view profGPA as
select *,
(a * 4 + b * 3 + c * 2 + d)
/
(a + b + c + d + f) as gpa
from prof;
create view studentGPA as
select *,
(a * 4 + b * 3 + c * 2 + d)
/
(a + b + c + d + f) as gpa
from student;
i'd rather be able to do this--
create function gpa( unknowntableTuple ) returns float8 as '
select
($1.a * 4 + $1.b * 3 + $1.c * 2 + $1.d)
/
($1.a + $1.b + $1.c + $1.d + $1.f)
' language 'sql';
any chance of working something like that? if so, how? if not,
well, waaah!
--
does a brain cell think?
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Thu, Mar 29, 2001 at 01:17:29PM -0600, will trillich wrote:
> is this kind of thing possible---?
>
> select gpa(student) from student where id=7121;
> select gpa(course) from course where id=29931;
> select gpa(prof) from prof where id=1321;
SELECT sum(grade) / count(grade) As GPA FROM grades;
^^^^ (bad juju if 0)
Where grades is;
create table grades (
exam int4,
who int4,
grade real,
PRIMARY KEY (exam, who),
FOREIGN KEY (who) REFERENCES student (student_id)
);
I'm not sure why you have a separate column for each grade... Probably
missing something...
> i've got several tables each of which have
>
> create table <various-and-sundry> (
> ...
> a int4,
> b int4,
> c int4,
> d int4,
> f int4,
> ...
> );
>
> since i'd like to AVOID this nonsense--
>
> create view courseGPA as
> select *,
> (a * 4 + b * 3 + c * 2 + d)
> /
> (a + b + c + d + f) as gpa
> from course;
>
> create view profGPA as
> select *,
> (a * 4 + b * 3 + c * 2 + d)
> /
> (a + b + c + d + f) as gpa
> from prof;
>
> create view studentGPA as
> select *,
> (a * 4 + b * 3 + c * 2 + d)
> /
> (a + b + c + d + f) as gpa
> from student;
>
> i'd rather be able to do this--
>
> create function gpa( unknowntableTuple ) returns float8 as '
> select
> ($1.a * 4 + $1.b * 3 + $1.c * 2 + $1.d)
> /
> ($1.a + $1.b + $1.c + $1.d + $1.f)
> ' language 'sql';
>
> any chance of working something like that? if so, how? if not,
> well, waaah!
Maybe use a 'plpgsql' function (don't think plain SQL functions will
take tuples as an argument).
--
Eric G. Miller <egm2@jps.net>
> SELECT sum(grade) / count(grade) As GPA FROM grades; > ^^^^ (bad juju if 0) No kidding... that kid totally failed ALL his classes! And before someone points it out, yes I saw the DBZ. Greg
On Thu, Mar 29, 2001 at 06:05:04PM -0800, Eric G. Miller wrote:
> On Thu, Mar 29, 2001 at 01:17:29PM -0600, will trillich wrote:
> > is this kind of thing possible---?
> >
> > select gpa(student) from student where id=7121;
> > select gpa(course) from course where id=29931;
> > select gpa(prof) from prof where id=1321;
>
> SELECT sum(grade) / count(grade) As GPA FROM grades;
> ^^^^ (bad juju if 0)
> Where grades is;
>
> create table grades (
> exam int4,
> who int4,
> grade real,
> PRIMARY KEY (exam, who),
> FOREIGN KEY (who) REFERENCES student (student_id)
> );
>
> I'm not sure why you have a separate column for each grade... Probably
> missing something...
also want to keep statistics on /how many/ F's, A's, etc.
one F, one A give the same GPA as two C's.
select * from course where a > 2 * f ;
but back to the original question --
even using PLPGSQL, is it possible to send VARYING relation
tuples to a procedure/function -- so long as the attributes
(fields) munged within the function are common to all tables?
create function gpa ( opaque ) returns float8 as '
declare
rec alias for $1;
begin
return (rec.D + (2*rec.C) + (3*rec.B) + (4*rec.A))
/ (rec.F + rec.D + rec.C + rec.B + rec.A);
end;' language 'plpgsql';
here, REC could be
create table course (
topic varchar(6),
num int4,
name varchar(80),
a int4,
b int4,
c int4,
d int4,
f int4
);
or
create table student (
id serial,
name varchar(80),
a int4,
b int4,
c int4,
d int4,
f int4
);
or
create table prof (
id serial,
name varchar(80),
office varchar(40),
phone varchar(10),
a int4,
b int4,
c int4,
d int4,
f int4
);
i'm hoping the same function could handle any of those different
tuple types so long as the attributes (fields) accessed are
common to all of them. impossible?
--
does a brain cell think?
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Fri, Mar 30, 2001 at 12:27:39AM -0500, Gregory Wood wrote: > > SELECT sum(grade) / count(grade) As GPA FROM grades; > > ^^^^ (bad juju if 0) > > No kidding... that kid totally failed ALL his classes! And before someone > points it out, yes I saw the DBZ. dbz? -- does a brain cell think? will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
On Fri, Mar 30, 2001 at 06:40:13PM -0600, will trillich wrote:
> > I'm not sure why you have a separate column for each grade... Probably
> > missing something...
>
> also want to keep statistics on /how many/ F's, A's, etc.
> one F, one A give the same GPA as two C's.
I see.
> select * from course where a > 2 * f ;
>
> but back to the original question --
>
> even using PLPGSQL, is it possible to send VARYING relation
> tuples to a procedure/function -- so long as the attributes
> (fields) munged within the function are common to all tables?
Easiest way would be a five parameter function, then it doesn't need to
know field names, just positions. I'm assuming you'll have a default of
zero for each grade field (simplifies NULL handling). An aggregate
function might be more elegant (but certainly more work). Guess this
school doesn't use the +/- modifiers?
-- UNTESTED! Will accept any integer for a grade count...
CREATE FUNCTION gpa (int4, int4, int4, int4, int4)
-- "a" "b" "c" "d" "f"
RETURNS float8 As '
DECLARE
numer float8 NOT NULL;
denom int4 NOT NULL;
BEGIN
numer := 4.0 * $1 + 3.0 * $2 + 2.0 * $3 + 1.0 * $4;
denom := $1 + $2 + $3 + $4 + $5;
IF denom < 1 THEN
RETURN 0.0;
END IF;
RETURN numer / denom;
END;
' LANGUAGE 'plpgsql';
SELECT topic, num, name, gpa(a,b,c,d,f) As gpa FROM course;
>
> create function gpa ( opaque ) returns float8 as '
> declare
> rec alias for $1;
> begin
> return (rec.D + (2*rec.C) + (3*rec.B) + (4*rec.A))
> / (rec.F + rec.D + rec.C + rec.B + rec.A);
> end;' language 'plpgsql';
>
> here, REC could be
>
> create table course (
> topic varchar(6),
> num int4,
> name varchar(80),
> a int4,
> b int4,
> c int4,
> d int4,
> f int4
> );
> or
> create table student (
> id serial,
> name varchar(80),
> a int4,
> b int4,
> c int4,
> d int4,
> f int4
> );
> or
> create table prof (
> id serial,
> name varchar(80),
> office varchar(40),
> phone varchar(10),
> a int4,
> b int4,
> c int4,
> d int4,
> f int4
> );
>
> i'm hoping the same function could handle any of those different
> tuple types so long as the attributes (fields) accessed are
> common to all of them. impossible?
--
Eric G. Miller <egm2@jps.net>
On Fri, Mar 30, 2001 at 06:49:51PM -0600, will trillich wrote: > On Fri, Mar 30, 2001 at 12:27:39AM -0500, Gregory Wood wrote: > > > SELECT sum(grade) / count(grade) As GPA FROM grades; > > > ^^^^ (bad juju if 0) > > > > No kidding... that kid totally failed ALL his classes! And before someone > > points it out, yes I saw the DBZ. > > > dbz? Division By Zero. Also, the above would perform integer division, would need a cast to 'float8' on one of the operands. -- Eric G. Miller <egm2@jps.net>
On Fri, Mar 30, 2001 at 06:34:45PM -0800, Eric G. Miller wrote: > On Fri, Mar 30, 2001 at 06:40:13PM -0600, will trillich wrote: > > even using PLPGSQL, is it possible to send VARYING relation > > tuples to a procedure/function -- so long as the attributes > > (fields) munged within the function are common to all tables? > > Easiest way would be a five parameter function, then it doesn't need to > know field names, just positions. I'm assuming you'll have a default of fooey. i was hoping... > zero for each grade field (simplifies NULL handling). An aggregate > function might be more elegant (but certainly more work). Guess this > school doesn't use the +/- modifiers? only cosmetically. (a+,a,a- == 4.0) > -- UNTESTED! Will accept any integer for a grade count... > > CREATE FUNCTION gpa (int4, int4, int4, int4, int4) > -- "a" "b" "c" "d" "f" > RETURNS float8 As ' > DECLARE > numer float8 NOT NULL; > denom int4 NOT NULL; why NOT NULL ? > BEGIN > numer := 4.0 * $1 + 3.0 * $2 + 2.0 * $3 + 1.0 * $4; > denom := $1 + $2 + $3 + $4 + $5; > > IF denom < 1 THEN > RETURN 0.0; > END IF; > > RETURN numer / denom; > END; > ' LANGUAGE 'plpgsql'; and here i had my hopes up for a universal "no record to big or too small" function argument... thanks! -- does a brain cell think? will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
On Sat, Mar 31, 2001 at 12:17:46AM -0600, will trillich wrote:
> On Fri, Mar 30, 2001 at 06:34:45PM -0800, Eric G. Miller wrote:
> > On Fri, Mar 30, 2001 at 06:40:13PM -0600, will trillich wrote:
> > > even using PLPGSQL, is it possible to send VARYING relation
> > > tuples to a procedure/function -- so long as the attributes
> > > (fields) munged within the function are common to all tables?
> >
> > Easiest way would be a five parameter function, then it doesn't need to
> > know field names, just positions. I'm assuming you'll have a default of
>
> fooey. i was hoping...
I think you still can have a function that uses field names. But then
your field names *have* to be the same. That's the only difference.
> > zero for each grade field (simplifies NULL handling). An aggregate
> > function might be more elegant (but certainly more work). Guess this
> > school doesn't use the +/- modifiers?
>
> only cosmetically. (a+,a,a- == 4.0)
>
> > -- UNTESTED! Will accept any integer for a grade count...
> >
> > CREATE FUNCTION gpa (int4, int4, int4, int4, int4)
> > -- "a" "b" "c" "d" "f"
> > RETURNS float8 As '
> > DECLARE
> > numer float8 NOT NULL;
> > denom int4 NOT NULL;
>
> why NOT NULL ?
Because the function doesn't handle the NULL cases. What is NULL + 3 ?
It's NULL. You could handle NULLs, it'll just need a bunch of tests
IF NOT RECORD.a ISNULL THEN
numer := numer + 4.0 * RECORD.a;
denom := denom + RECORD.a;
END IF;
Or some such...
> > BEGIN
> > numer := 4.0 * $1 + 3.0 * $2 + 2.0 * $3 + 1.0 * $4;
> > denom := $1 + $2 + $3 + $4 + $5;
> >
> > IF denom < 1 THEN
> > RETURN 0.0;
> > END IF;
> >
> > RETURN numer / denom;
> > END;
> > ' LANGUAGE 'plpgsql';
>
> and here i had my hopes up for a universal "no record to big or
> too small" function argument... thanks!
--
Eric G. Miller <egm2@jps.net>
On Sat, Mar 31, 2001 at 11:52:47AM -0800, Eric G. Miller wrote:
> I think you still can have a function that uses field names. But then
> your field names *have* to be the same. That's the only difference.
i've tried
create function gpa(OPAQUE) ....
and psql tells me it successfully
CREATED
something, but
\df
shows no such function.
if i do
create function gpa(classTable) ...
then it probably won't be called via
select gpa(studentTable) ...
what arg to i specify to allow varying records (with a subset of
exactly-the-same-names field subset) to be passed to a function?
--
does a brain cell think?
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Mon, Apr 02, 2001 at 01:47:29AM -0500, will trillich wrote: > On Sat, Mar 31, 2001 at 11:52:47AM -0800, Eric G. Miller wrote: > > I think you still can have a function that uses field names. But then > > your field names *have* to be the same. That's the only difference. > > i've tried > > create function gpa(OPAQUE) .... > > and psql tells me it successfully > > CREATED > > something, but > > \df > > shows no such function. > > if i do > > create function gpa(classTable) ... > > then it probably won't be called via > > select gpa(studentTable) ... > > what arg to i specify to allow varying records (with a subset of > exactly-the-same-names field subset) to be passed to a function? The trickery only seems to work for trigger functions. You can duplicate the function definition for each table. PostgreSQL will differentiate them not only by name but parameters, so they can all be called "gpa". I still think the five parameter function is the most general approach... -- Eric G. Miller <egm2@jps.net>
will trillich <will@serensoft.com> writes:
> i've tried
> create function gpa(OPAQUE) ....
> and psql tells me it successfully
> CREATED
> something, but
> \df
> shows no such function.
I believe \df suppresses opaque-argument functions, on the theory that
they're probably just I/O functions. But OPAQUE won't do you any good
anyway unless you code the function in C.
> what arg to i specify to allow varying records (with a subset of
> exactly-the-same-names field subset) to be passed to a function?
Inheritance seems to work for this in current sources:
regression=# create table p1 (f1 int);
CREATE
regression=# create table p2 (f2 int) inherits (p1);
CREATE
regression=# create function getf1(p1) returns int as '
regression'# select $1.f1' language 'sql';
CREATE
regression=# insert into p1 values(1);
INSERT 1030276 1
regression=# insert into p2 values(11,22);
INSERT 1030277 1
regression=# select getf1(p1) from p1;
getf1
-------
1
11
(2 rows)
regression=#
regards, tom lane
On Mon, Apr 02, 2001 at 10:21:34AM -0400, Tom Lane wrote: > will trillich <will@serensoft.com> writes: > > i've tried > > create function gpa(OPAQUE) .... > > and psql tells me it successfully > > CREATED > > something, but > > \df > > shows no such function. > > I believe \df suppresses opaque-argument functions, on the theory that > they're probably just I/O functions. But OPAQUE won't do you any good > anyway unless you code the function in C. > > > what arg to i specify to allow varying records (with a subset of > > exactly-the-same-names field subset) to be passed to a function? > > Inheritance seems to work for this in current sources: > > regression=# create table p1 (f1 int); > CREATE > regression=# create table p2 (f2 int) inherits (p1); > CREATE > regression=# create function getf1(p1) returns int as ' > regression'# select $1.f1' language 'sql'; > CREATE > regression=# insert into p1 values(1); > INSERT 1030276 1 > regression=# insert into p2 values(11,22); > INSERT 1030277 1 > regression=# select getf1(p1) from p1; > getf1 > ------- > 1 > 11 > (2 rows) ding! light comes on ... never thought of that. mucho cool, mucho thanks! lots of new exploring to delve into now... -- does a brain cell think? will@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!
On Mon, Apr 02, 2001 at 02:02:51PM -0500, will trillich wrote:
> On Mon, Apr 02, 2001 at 10:21:34AM -0400, Tom Lane wrote:
> > Inheritance seems to work for this in current sources:
> >
> > regression=# create table p1 (f1 int);
> > CREATE
> > regression=# create table p2 (f2 int) inherits (p1);
> > CREATE
> > regression=# create function getf1(p1) returns int as '
> > regression'# select $1.f1' language 'sql';
> > CREATE
> > regression=# insert into p1 values(1);
> > INSERT 1030276 1
> > regression=# insert into p2 values(11,22);
> > INSERT 1030277 1
> > regression=# select getf1(p1) from p1;
> > getf1
> > -------
> > 1
> > 11
> > (2 rows)
>
> ding! light comes on ... never thought of that. mucho cool, mucho
> thanks! lots of new exploring to delve into now...
clunk, light dims. "current sources" means "not what i'm using".
being a staunch potato (debian 2.2) user, i'm already on the
leading edge, having upgraded my postgresql to 7.0.3 ...
i'll go with the
define function gpa(int,int,int,int,int) returns float ...
i suppose, then. hmph.
--
americans should never read anything so subversive as what's at
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!