Skip site navigation (1) Skip section navigation (2)

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

From: "Eric G(dot) Miller" <egm2(at)jps(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: "Eric G(dot) Miller" <egm2(at)jps(dot)net>
Subject: Re: function to operate on same fields, different records?
Date: 2001-03-31 02:34:45
Message-ID: 20010330183445.C29151@calico.local (view raw or flat)
Thread:
Lists: 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(at)jps(dot)net>

In response to

Responses

pgsql-general by date

Next:From: Eric G. MillerDate: 2001-03-31 02:37:40
Subject: Re: Re: function to operate on same fields, different records?
Previous:From: Eric G. MillerDate: 2001-03-31 02:08:13
Subject: Re: storing "small binary objects"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group