Received: from kds5.kivex.com (kds5.kivex.com [204.177.32.2]) by hub.org (8.9.2/8.9.1) with ESMTP id LAA19071 for ; Tue, 13 Apr 1999 11:13:19 -0400 (EDT) (envelope-from gwaldron@wareonearth.com) Received: from glenn (www.wareonearth.com [216.50.3.34]) by kds5.kivex.com (8.8.8/8.8.7-KIVEX) with SMTP id LAA25607 for ; Tue, 13 Apr 1999 11:13:17 -0400 (EDT) Message-ID: <003801be85c0$d4aac910$0800a8c0@glenn.kivex.com> From: "Glenn Waldron" To: Subject: Trouble with null text fields! Date: Tue, 13 Apr 1999 11:18:05 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 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 --- Glenn Waldron, WareOnEarth Inc. * v 703/847-2969 * f 703/847-2538