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(at)jps(dot)net>