Re: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL - Mailing list pgsql-hackers

From jwieck@debis.com (Jan Wieck)
Subject Re: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL
Date
Msg-id m0zbW3a-000EBYC@orion.SAPserv.Hamburg.dsh.de
Whole thread Raw
In response to RE: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL  ("Jackson, DeJuan" <djackson@cpsgroup.com>)
List pgsql-hackers
> >     Don't blame PL/pgSQL for that.  There is only one bool isNull
> >     pointer given to PL handlers. How should the PL handler know,
> >     which  of  the arguments are null then?  As I said on another
> >     thread, the function call interface needs to get  redesigned.
> Well, Jan, don't get sensitive.  I love PL/pgSQL. And I had no illusions
> that it was your HANDLER causing the problem.  I feel that a function
> call interface redesign is also needed.  But, I do have a quick
> question, why does it matter which one is NULL if you can still obtain
> the parameters in the order they were passed why would one become NULL
> that wasn't before?   I'm asking totally from ignorance here.

    It might be possible, that even if *isNull is true to look at
    the actual arguments given to the PL handler.  For  datatypes
    passed  by  reference, a NULL value has to get passed as null
    pointer. I'm not 100% sure if that  is  really  true  in  all
    cases where PL functions can get called, and we all know what
    happens when accessing a pointer  that  points  to  something
    else than a memory location. For arguments passed by value it
    is totally impossible to know if it's a NULL  by  looking  at
    the value itself.

    Summary  is,  that the PL handler cannot be sure which of the
    arguments the function caller meant when calling with *isNull
    = TRUE.  And I decided for now to be safe and assume he meant
    all.

    When accessing data from a specific table, it is possible  to
    call  the  function  with  a  complex  type.  This  time, the
    PL/pgSQL function gets the complete tuple and can look at the
    information there which attributes are NULLs.

        CREATE TABLE a (k integer, i integer);

        CREATE FUNCTION a_i_checknull(a) RETURNS bool AS '
        DECLARE
            row_a ALIAS FOR $1; -- The dot-notation $1.i does not work!
        BEGIN
            IF row_a.k ISNULL THEN
                RAISE NOTICE ''attribute k has NULL value'';
            END IF;
            IF row_a.i ISNULL THEN
                RAISE NOTICE ''attribute i has NULL value'';
            END IF;
            IF row_a.k ISNULL OR row_a.i ISNULL THEN
                RETURN ''t'';
            END IF;
            RETURN ''f'';
        END;
        ' LANGUAGE 'plpgsql';


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

pgsql-hackers by date:

Previous
From: "Jackson, DeJuan"
Date:
Subject: RE: [HACKERS] Re: bug on aggregate function AVG()
Next
From: Tom Lane
Date:
Subject: Re: [INTERFACES] crypt not included when compiling libpgtcl !!!!!!!