Re: function to operate on same fields, different records? - Mailing list pgsql-general

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!