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
Subject: Re: function to operate on same fields, different records?
Date: 2001-03-30 02:05:04
Message-ID: 20010329180504.A14869@calico.local (view raw or flat)
Thread:
Lists: pgsql-general
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(at)jps(dot)net>

In response to

Responses

pgsql-general by date

Next:From: JackDate: 2001-03-30 03:13:56
Subject: PostGreSql 7.1
Previous:From: Alfonso PenicheDate: 2001-03-30 00:37:38
Subject: Re: create user

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