Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.2/8.9.1) with ESMTP id MAA30828 for ; Tue, 13 Apr 1999 12:10:06 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id SAA06867; Tue, 13 Apr 1999 18:09:58 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10X6cQ-0007Mf-00; Tue, 13 Apr 1999 17:05:02 +0000 Message-ID: <37136BD9.9739CA5@sferacarta.com> Date: Tue, 13 Apr 1999 18:07:53 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Glenn Waldron CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Trouble with null text fields References: <004401be85c1$e21175d0$0800a8c0@glenn.kivex.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Glenn Waldron ha scritto: > 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 is the syntax that PostgreSQL understands: select case when field_one is null then t||'?' else t||v 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; > Seems that PostgreSQL doesn't recognize this syntax yet. > > 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 Try this one: create function coalesce(text) returns text as 'declare nonullo ALIAS FOR $1; begin IF nonullo ISNULL THEN RETURN ''\\\\N''; ELSE RETURN nonullo; END IF; end; ' language 'plpgsql';