Horton

Thread

  1. function to operate on same fields, different records?

    Will Trillich <will@serensoft.com> — 2001-03-29T19:17:29Z

    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;

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!

-- 
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!

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

    Eric G. Miller <egm2@jps.net> — 2001-03-30T02:05:04Z

    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@jps.net>

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

    Gregory Wood <gregw@com-stock.com> — 2001-03-30T05:27:39Z

    > SELECT sum(grade) / count(grade) As GPA FROM grades;
>                     ^^^^ (bad juju if 0)

No kidding... that kid totally failed ALL his classes! And before someone
points it out, yes I saw the DBZ.

Greg

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

    Will Trillich <will@serensoft.com> — 2001-03-31T00:40:13Z

    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!

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

    Will Trillich <will@serensoft.com> — 2001-03-31T00:49:51Z

    On Fri, Mar 30, 2001 at 12:27:39AM -0500, Gregory Wood wrote:
> > SELECT sum(grade) / count(grade) As GPA FROM grades;
> >                     ^^^^ (bad juju if 0)
> 
> No kidding... that kid totally failed ALL his classes! And before someone
> points it out, yes I saw the DBZ.


dbz?


-- 
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!

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

    Eric G. Miller <egm2@jps.net> — 2001-03-31T02:34:45Z

    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>

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

    Eric G. Miller <egm2@jps.net> — 2001-03-31T02:37:40Z

    On Fri, Mar 30, 2001 at 06:49:51PM -0600, will trillich wrote:
> On Fri, Mar 30, 2001 at 12:27:39AM -0500, Gregory Wood wrote:
> > > SELECT sum(grade) / count(grade) As GPA FROM grades;
> > >                     ^^^^ (bad juju if 0)
> > 
> > No kidding... that kid totally failed ALL his classes! And before someone
> > points it out, yes I saw the DBZ.
> 
> 
> dbz?

Division By Zero.  Also, the above would perform integer division, would
need a cast to 'float8' on one of the operands.

-- 
Eric G. Miller <egm2@jps.net>

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

    Will Trillich <will@serensoft.com> — 2001-03-31T06:17:46Z

    On Fri, Mar 30, 2001 at 06:34:45PM -0800, Eric G. Miller wrote:
> On Fri, Mar 30, 2001 at 06:40:13PM -0600, will trillich wrote:
> > 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

fooey. i was hoping...

> 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?

only cosmetically. (a+,a,a- == 4.0)

> -- 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;

why 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';

and here i had my hopes up for a universal "no record to big or
too small" function argument... thanks!

-- 
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!

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

    Eric G. Miller <egm2@jps.net> — 2001-03-31T19:52:47Z

    On Sat, Mar 31, 2001 at 12:17:46AM -0600, will trillich wrote:
> On Fri, Mar 30, 2001 at 06:34:45PM -0800, Eric G. Miller wrote:
> > On Fri, Mar 30, 2001 at 06:40:13PM -0600, will trillich wrote:
> > > 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
> 
> fooey. i was hoping...

I think you still can have a function that uses field names.  But then
your field names *have* to be the same.  That's the only difference.

> > 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?
> 
> only cosmetically. (a+,a,a- == 4.0)
> 
> > -- 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;
> 
> why NOT NULL ?

Because the function doesn't handle the NULL cases.  What is NULL + 3 ?
It's NULL.  You could handle NULLs, it'll just need a bunch of tests

  IF NOT RECORD.a ISNULL THEN
     numer := numer + 4.0 * RECORD.a;
     denom := denom + RECORD.a;
  END IF;

Or some such...

> >     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';
> 
> and here i had my hopes up for a universal "no record to big or
> too small" function argument... thanks!

-- 
Eric G. Miller <egm2@jps.net>

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

    Will Trillich <will@serensoft.com> — 2001-04-02T06:47:29Z

    On Sat, Mar 31, 2001 at 11:52:47AM -0800, Eric G. Miller wrote:
> I think you still can have a function that uses field names.  But then
> your field names *have* to be the same.  That's the only difference.

i've tried

	create function gpa(OPAQUE) ....

and psql tells me it successfully

	CREATED

something, but

	\df

shows no such function.

if i do

	create function gpa(classTable) ...

then it probably won't be called via

	select gpa(studentTable) ...

what arg to i specify to allow varying records (with a subset of
exactly-the-same-names field subset) to be passed to a function?

-- 
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!

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

    Eric G. Miller <egm2@jps.net> — 2001-04-02T08:04:19Z

    On Mon, Apr 02, 2001 at 01:47:29AM -0500, will trillich wrote:
> On Sat, Mar 31, 2001 at 11:52:47AM -0800, Eric G. Miller wrote:
> > I think you still can have a function that uses field names.  But then
> > your field names *have* to be the same.  That's the only difference.
> 
> i've tried
> 
> 	create function gpa(OPAQUE) ....
> 
> and psql tells me it successfully
> 
> 	CREATED
> 
> something, but
> 
> 	\df
> 
> shows no such function.
> 
> if i do
> 
> 	create function gpa(classTable) ...
> 
> then it probably won't be called via
> 
> 	select gpa(studentTable) ...
> 
> what arg to i specify to allow varying records (with a subset of
> exactly-the-same-names field subset) to be passed to a function?

The trickery only seems to work for trigger functions.

You can duplicate the function definition for each table.  PostgreSQL
will differentiate them not only by name but parameters, so they can all
be called "gpa".  I still think the five parameter function is the most
general approach...

-- 
Eric G. Miller <egm2@jps.net>

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-02T14:21:34Z

    will trillich <will@serensoft.com> writes:
> i've tried
> 	create function gpa(OPAQUE) ....
> and psql tells me it successfully
> 	CREATED
> something, but
> 	\df
> shows no such function.

I believe \df suppresses opaque-argument functions, on the theory that
they're probably just I/O functions.  But OPAQUE won't do you any good
anyway unless you code the function in C.

> what arg to i specify to allow varying records (with a subset of
> exactly-the-same-names field subset) to be passed to a function?

Inheritance seems to work for this in current sources:

regression=# create table p1 (f1 int);
CREATE
regression=# create table p2 (f2 int) inherits (p1);
CREATE
regression=# create function getf1(p1) returns int as '
regression'# select $1.f1' language 'sql';
CREATE
regression=# insert into p1 values(1);
INSERT 1030276 1
regression=# insert into p2 values(11,22);
INSERT 1030277 1
regression=# select getf1(p1) from p1;
 getf1
-------
     1
    11
(2 rows)

regression=#

			regards, tom lane

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

    Will Trillich <will@serensoft.com> — 2001-04-02T19:02:51Z

    On Mon, Apr 02, 2001 at 10:21:34AM -0400, Tom Lane wrote:
> will trillich <will@serensoft.com> writes:
> > i've tried
> > 	create function gpa(OPAQUE) ....
> > and psql tells me it successfully
> > 	CREATED
> > something, but
> > 	\df
> > shows no such function.
> 
> I believe \df suppresses opaque-argument functions, on the theory that
> they're probably just I/O functions.  But OPAQUE won't do you any good
> anyway unless you code the function in C.
> 
> > what arg to i specify to allow varying records (with a subset of
> > exactly-the-same-names field subset) to be passed to a function?
> 
> Inheritance seems to work for this in current sources:
> 
> regression=# create table p1 (f1 int);
> CREATE
> regression=# create table p2 (f2 int) inherits (p1);
> CREATE
> regression=# create function getf1(p1) returns int as '
> regression'# select $1.f1' language 'sql';
> CREATE
> regression=# insert into p1 values(1);
> INSERT 1030276 1
> regression=# insert into p2 values(11,22);
> INSERT 1030277 1
> regression=# select getf1(p1) from p1;
>  getf1
> -------
>      1
>     11
> (2 rows)

ding! light comes on ... never thought of that. mucho cool, mucho
thanks! lots of new exploring to delve into now...

-- 
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!

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

    Will Trillich <will@serensoft.com> — 2001-04-13T06:35:35Z

    On Mon, Apr 02, 2001 at 02:02:51PM -0500, will trillich wrote:
> On Mon, Apr 02, 2001 at 10:21:34AM -0400, Tom Lane wrote:
> > Inheritance seems to work for this in current sources:
> > 
> > regression=# create table p1 (f1 int);
> > CREATE
> > regression=# create table p2 (f2 int) inherits (p1);
> > CREATE
> > regression=# create function getf1(p1) returns int as '
> > regression'# select $1.f1' language 'sql';
> > CREATE
> > regression=# insert into p1 values(1);
> > INSERT 1030276 1
> > regression=# insert into p2 values(11,22);
> > INSERT 1030277 1
> > regression=# select getf1(p1) from p1;
> >  getf1
> > -------
> >      1
> >     11
> > (2 rows)
> 
> ding! light comes on ... never thought of that. mucho cool, mucho
> thanks! lots of new exploring to delve into now...

clunk, light dims. "current sources" means "not what i'm using".

being a staunch potato (debian 2.2) user, i'm already on the
leading edge, having upgraded my postgresql to 7.0.3 ...

i'll go with the

	define function gpa(int,int,int,int,int) returns float ...

i suppose, then. hmph.

-- 
americans should never read anything so subversive as what's at
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!