PL/pgSQL NOT NULL variables 
Author Message
 PL/pgSQL NOT NULL variables

This is my first attempt at using PL/pgSQL so I may be carrying around
a grotesque misconception of some sort...

I can't get NOT NULL variables to work at all in PL/pgSQL. Here's a
simple function which uses a default value for a variable:

test=> create function t1() returns integer as '
test'> declare foo integer := 42;
test'> begin return foo; end;
test'> ' language 'plpgsql';
CREATE
test=> select t1();
 t1
----
 42
(1 row)

No problem at all. But if I create a function exactly identical except
for making the variable NOT NULL:

test=> create function t2() returns integer as '
test'> declare foo integer not null := 42;
test'> begin return foo; end;
test'> ' language 'plpgsql';
CREATE
test=> select t2();
ERROR:  NULL assignment to variable 'foo' declared NOT NULL

I've tried using DEFAULT instead of :=, which the docs suggest should
be equivalent, and the same thing happens. Similarly when I use type
"text". As there are no NOT NULL variables in the examples or the
regression tests, I suspect I may have found a bug. Does anybody have
NOT NULL working in a PL/pgSQL function? Can anybody shed any light
on what I may be doing wrong?

7.02 on Debian GNU/Linux, FWIW.

Richard



Wed, 25 Jun 2003 00:42:07 GMT
 PL/pgSQL NOT NULL variables

Quote:

> I can't get NOT NULL variables to work at all in PL/pgSQL.

Looks like a bug to me too ... will look into it.

                        regards, tom lane



Wed, 25 Jun 2003 01:27:42 GMT
 PL/pgSQL NOT NULL variables

Quote:

> I can't get NOT NULL variables to work at all in PL/pgSQL.

Doesn't look like they've ever worked :-(.  If you need a patch for
7.0.*, line 1907 of src/pl/plpgsql/src/pl_exec.c should read

            if (*isNull && var->notnull)

not

            if (isNull && var->notnull)

                        regards, tom lane



Wed, 25 Jun 2003 09:48:47 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. not null column during CTAS(not null definition did not come across)

2. Passing a null value in pl/pgsql

3. pl/pgsql and global variables

4. Dynamic Queries in pl/pgsql [w/variable substitution]

5. A IS NOT NULL Vs Not A IS NULL

6. NOT NULL vs NOT NULL

7. Schemas not available for pl/pgsql %TYPE....

8. Schemas not available for pl/pgsql %TYPE....

9. NULL problems assigning to variables from SQL 7.0 database to variables

10. pgsql/src/pl/plpgsql/src Tag: REL7_2_STABLE pl ...

11. Converting PL/SQL to PL/PGSQL

12. Working w/Oracle 7.3.4 LONG variables as VARCHAR2 variables in PL/SQL


 
Powered by phpBB® Forum Software