Using the below (with a few fixes, delcare->declare
and adding the close quote and language type as plpgsql,
and a table1 defined as id int, field1 varchar, field2 varchar,
I get a NULL returned if I select test1(); I'm somewhere
between beta4 and 5 though.
On Sun, 18 Mar 2001, Josh Berkus wrote:
> Tom,
>
> > Yes, I'd say so. 7.1 returns a NULL as expected ...
>
> Er, Tom, I'm using 7.1.
>
> Pgsql does return a null as expected when going from the command line.
> However, if I call the regular expression from within a PL/pgSQL
> function, it bombs.
>
> Therefore, please forward to pgsql-bugs:
>
> Bug Name: Regular Expressions with NULL error out in PL/pgSQL.
> Can Reproduce: Reliably
> Severity: Annoyance.
> Location: PL/pgSQL / Function handler / SPI
> Version: 7.1 beta 3
>
> Description: If you attempt to run a query within a PL/pgSQL function
> which compares a field. via regular expression, to a NULL variable, you
> get a fatal error.
>
> Example:
>
> CREATE FUNCTION test1 () RETURNS INT4 AS
> 'DELCARE
> expression1 VARCHAR;
> return_value INT4;
> BEGIN
> expression1 := NULL;
> SELECT id INTO return_value
> FROM table1
> WHERE field2 ~* expression1;
> RETURN return_value;
> END;