Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2J4A1670826 for ; Sun, 18 Mar 2001 23:10:01 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 26483; Sun, 18 Mar 2001 20:09:59 -0800 From: "Josh Berkus" Subject: Re: What do I do with this error? To: Tom Lane Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Sun, 18 Mar 2001 20:09:59 -0800 Message-ID: In-Reply-To: <3013.984971455@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200103/348 X-Sequence-Number: 2012 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; -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco