Re: Referencing uninitialized variables in plpgsql

On Feb 5, 2005, at 11:20 PM, Karl O. Pinc wrote:

> Is this example telling me I get NULL for unitialized references?
> I don't believe I should count on this behavior unless it's
> documented, should I?
>
> => create or replace function foo() returns int language plpgsql
> as 'declare a int; b int; begin a := b; return a; end; ';
> CREATE FUNCTION
> => select foo();

Yes, exactly. If you don't assign a value to a declared pspgsql
variable, it is NULL. Operations on NULL variables are no different
than operations on NULL values in the database. If you are concerned
about this, then always assign a value when you declare it.

Also, you can specify NOT NULL in your declaration to ensure a runtime
error is generated if the variable is null. See:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-declarations.html

The general syntax of a variable declaration is:
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

The DEFAULT clause, if given, specifies the initial value assigned to
the variable when the block is entered. If the DEFAULT clause is not
given then the variable is initialized to the SQL null value. The
CONSTANT option prevents the variable from being assigned to, so that
its value remains constant for the duration of the block. If NOT NULL
is specified, an assignment of a null value results in a run-time
error. All variables declared as NOT NULL must have a nonnull default
value specified.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL