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

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