Re: function to operate on same fields, different records?

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(at)jps(dot)net>