Hi all,

----I'm trying to understand this very interesting language PL/pgSQL
thanks to Jan Wieck,
----finally we can trap NULL values as in:

create function len(text) returns text as '
declare
        nonullo alias for $1;
begin
     if nonullo then
        return length(nonullo);
     else
        return ''<NULL>'';
     end if;
end;' language 'plpgsql';
CREATE

select len(c) from a;
len
------
12
<NULL>
<NULL>
<NULL>
12
(5 rows)


------I'm trying to create a function that returns a value instead of
NULL.
------The following one works well:

create function nvl(integer) returns integer as '
declare
        nullo   integer := 0;
        nonullo alias for $1;
begin
        if NONULLO then
                return NONULLO;
        else
                return NULLO;
        end if;
end;' language 'plpgsql';
CREATE

select nvl(i) from a;
    nvl
-------
      0
      0
2232767
      0
(4 rows)


------I would like to report this little thing, seems PL/pgSQL isn't
case insensitive:
drop function nvl(integer,integer);
DROP
create function nvl(integer) returns integer as '
declare
        NULLO   integer := 0;
        NONULLO alias for $1;
begin
        if NONULLO then
                return NONULLO;
        else
                return NULLO;
        end if;
end;' language 'plpgsql';
CREATE

select nvl(i) from a;
ERROR:  attribute 'nonullo' not found

-------and seems it returns only constant values:

drop function nvl(integer,integer);
DROP
create function nvl(integer,integer) returns integer as '
declare
        nonullo alias for $1;
        nullo   ALIAS FOR $2;
begin
        if NONULLO then
                return NONULLO;
        else
                return NULLO;
        end if;
end;' language 'plpgsql';
CREATE

select nvl(i,0) from a;
    nvl
-------


2232767

(4 rows)

                     Jose'

Wed, 04 Nov 1998 12:20:53 +0100
"Jose' Soares" <jose@sferacarta.com>
PL/pgSQL a great procedural language for PostgreSQL