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@jps.net>