Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA08350 for ; Sat, 26 Feb 2000 16:27:13 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id QAA03858; Sat, 26 Feb 2000 16:27:07 -0500 (EST) To: Peter Stamfest cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] text -> char In-reply-to: References: Comments: In-reply-to Peter Stamfest message dated "Sun, 13 Feb 2000 00:57:04 +0100" Date: Sat, 26 Feb 2000 16:27:06 -0500 Message-ID: <3855.951600426@sss.pgh.pa.us> From: Tom Lane Peter Stamfest wrote a couple weeks ago: > create view three as select CAST(substr(descr, 2, 4) as char(8)) as > d from one; That should work, and as of today it does work in current sources: regression=# create view three as select CAST(substr(descr, 2, 4) as char(8)) regression-# as d from one; CREATE 278003 1 regression=# \d three View "three" Attribute | Type | Modifier -----------+---------+---------- d | char(8) | View definition: SELECT (substr(one.descr, 2, 4))::char(8) AS d FROM one; Releases before 7.0 had a tendency to just discard casts :-( in a lot of corner cases, and this is one of 'em. I've cleaned up the cast problems I know about, but there may be some left... > The char() function works neither: > create view four as select char(substr(descr, 2, 4)) as d from one; > ERROR: parser: parse error at or near "substr" That's not a bug. CHAR is an SQL reserved word for a type --- the parser is expecting a type length indicator, like "char(8)", when it sees CHAR(. While there is a function named "char", you can only get at it by double-quoting the name so it no longer looks like a keyword: select "char"(substr(descr, 2, 4)) as d from one; Also note that this converts to the internal single-byte char type, which is presumably not what you wanted anyway. regards, tom lane