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

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>