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

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>