Thread

1. Bogus reporting of non-null value in function call.

   James Finch <james@volumefour.com> — 2000-04-24T22:40:44Z

   Platform: Linux-2.2.12-20 (RH 6.1)
   PostgreSQL: 7.0RC1
   
   Description:
   Arguments to a function seem to be incorrectly validated against constraints
   on the table on which it operates. For example: I have a table that defines
   one column (id) as a primary key, and also specifies the NOT NULL
   constraint. I then have a function performs an insert into this table. The
   function parameters basically map to the columns in this table. If I pass in
   null in any argument to the function, I always get the spurious error
   message "ERROR:  ExecAppend: Fail to add null value in not null attribute
   ...", even though I'm not passing in null for the parameter that maps to the
   column to which the error message applies.
   
   
   To reproduce.
   
   CREATE TABLE atom (
          id               VARCHAR(256) NOT NULL,
          name             VARCHAR(256),
          domain           VARCHAR(256),
          definition    	VARCHAR(256),
          value            VARCHAR(4000),
          num_value        FLOAT8,
          date_value       DATE,
          objtype          INT2,
          PRIMARY KEY (id)
   );
   
   
   
   DROP FUNCTION createAtom( INT2, VARCHAR, VARCHAR, VARCHAR, VARCHAR,
   VARCHAR );
   CREATE FUNCTION createAtom( INT2, VARCHAR, VARCHAR, VARCHAR, VARCHAR,
   VARCHAR )
   	RETURNS BOOL AS '
   		DECLARE
   			p_objtype ALIAS FOR $1;
   			p_idThis ALIAS FOR $2;
   			p_name ALIAS FOR $3;
   			p_domain ALIAS FOR $4;
   			p_definition ALIAS FOR $5;
   			p_value ALIAS FOR $6;
   		BEGIN
   			INSERT INTO atom ( objtype, id, name, domain, definition, num_value,
   value )
   			VALUES ( p_objtype, p_idThis, p_name, p_domain, p_definition, p_value,
   p_value ) ;
   			RETURN TRUE;
   		END;
   	' LANGUAGE 'plpgsql';
   
   select createAtom( 'abc', 'Fred', 'NT', 'Fred', 'a', 1.0, null, 2 );
   
   
   

2. Re: Bogus reporting of non-null value in function call.

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-24T22:56:56Z

   "James Finch" <James@VolumeFour.com> writes:
   > Arguments to a function seem to be incorrectly validated against constraints
   > on the table on which it operates.
   
   I think what's really going on here is that because the function manager
   interface defines only one isNull flag for a function call, *all* the
   parameters appear to be NULL if any one of them is actually NULL.
   (What's more, the function result will be taken to be NULL no matter
   what you return.)
   
   Fixing this is on the to-do list for 7.1.  It's not a trivial fix
   because so much code will have to be touched in order to change
   that interface :-(
   
   			regards, tom lane