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 19:52:47
Message-ID: 20010331115247.B32288@calico.local (view raw or flat)
Thread:
Lists: pgsql-general
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(at)jps(dot)net>

In response to

Responses

pgsql-general by date

Next:From: Andrew HammondDate: 2001-03-31 21:16:53
Subject: Re: A valuable addition to PHP...
Previous:From: Lincoln YeohDate: 2001-03-31 18:40:32
Subject: Re: Q: Record Updating/Locking in Web Environments

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