Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.2/8.9.1) with ESMTP id LAA27631 for ; Tue, 13 Apr 1999 11:52:24 -0400 (EDT) (envelope-from michael.davis@tvguide.com) Received: from prevuepo1.prevuenet.com (prevuepo1.uvsg.com [192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id H4MAVXQW; Tue, 13 Apr 1999 10:51:44 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2232.9) id ; Tue, 13 Apr 1999 10:54:44 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC145390@lambic.prevuenet.com> From: Michael Davis To: "'Glenn Waldron'" , pgsql-sql@postgreSQL.org Subject: RE: [SQL] Trouble with null text fields Date: Tue, 13 Apr 1999 10:48:33 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2232.9) Content-Type: text/plain Try this: DROP FUNCTION nz(text); CREATE FUNCTION nz (text) RETURNS text AS ' BEGIN if ($1 is NULL) then return ''''; else return $1; end if; END; ' LANGUAGE 'plpgsql'; SELECT (nz(field_one) || nz(field_two)) from t1; This has worked for me. It is a little slow. -----Original Message----- From: Glenn Waldron [SMTP:gwaldron@wareonearth.com] Sent: Tuesday, April 13, 1999 9:26 AM To: pgsql-sql@postgreSQL.org Subject: [SQL] Trouble with null text fields Using Postgres 6.5 beta (snap 12 apr), on Linux i386. I moved up from 6.4.2 when I couldn't get things working. I'm having difficulty dealing with null text/varchar fields. I need to be able to interpret null values as the null string '' for the purposes on concatenation. 1) ---- For example, the query: SELECT (field_one || field_two) from t1; Will return the concatenation of the two fields. If either of the fields is null, it is interpreted as the empty string '' and the correct answer is printed. But: SELECT * from t1 where ( field_one || field_two = 'something' ) This does NOT work is either field_one or field_two is null. Same result with the textcat() function. 2) ---- Next I tried using "case", getting a parse error at or near "then": SELECT ( case field_one when null then '' else field_one end ) from t1; This one gave me "ERROR: copyObject: don't know how to copy 704": SELECT ( case field_one when 'string' then 'other' else 'third' end) from t1; 3) --- I tried writing a function that takes a "text" type and returns '' is the string is null. Never could successfully do a null test on a function parameter. 4) ---- I also tried writing my own concat function, and found that passing null fields into a user function doesn't seem to work either. So I tried passing the whole thing in as a TUPLE, since you can determine whether a field is null with the GetAttributeByName() call. The creation: CREATE FUNCTION mycat(text,text) returns text as '/usr/.../file.so' langauge 'sql'; This worked great, even with null values: SELECT mycat(field_one, field_two) from t1; This crashed the backend, with a "Memory exhauted in AllocSetAlloc()" error: SELECT * from t1 where mycat(field_one, field_two) = 'something'; So I tried making an index, and got: "DefineIndex(): Attibute t1 not found" CREATE INDEX t1_ix on t1 ( mycat(t1) text_ops ); Any help is appreciated!! Sorry for the novel!! -glenn