X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 7EE6FD1B551
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 22:34:02 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 94350-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Oct 2003 19:33:34 -0300 (ADT)
Received: from me (dial-148-240-10-68.zone-1.dial.net.mx [148.240.10.68])
	by svr1.postgresql.org (Postfix) with SMTP id 672C5D1B4E8
	for <pgsql-sql@postgresql.org>; Thu, 16 Oct 2003 19:24:16 -0300 (ADT)
X-Mailer: Bare-Hands-v23.9
From: Yusuf <yusufad@myrealbox.com>
To: PgSql-SQL <pgsql-sql@postgresql.org>
Subject: Can't convert numeric to_char() in catenate function
Message-Id: <20031016222416.672C5D1B4E8@svr1.postgresql.org>
Date: Thu, 16 Oct 2003 19:24:16 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200310/195
X-Sequence-Number: 15595


Hi there.

Im trying to do the usual row catenation function, but for a "numeric" type column.  I have this:

CREATE OR REPLACE FUNCTION "verticat" (text, numeric) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL THEN $1
            WHEN $1 IS NULL OR $1 = '''' 
		THEN to_char($2,'999999999D99')
            ELSE $1 || '', '' || to_char($2,'99999999D99')
       END
' LANGUAGE 'sql';

This works without problem with "float" or other type with direct casting to text (after replacing the "to_char(...)" with $2::text), but this one gives me an "ERROR:  parser: parse error at or near "999999999""... wath is wrong with it??

It would also be nice to trim the spaces placed by to_char, with something like "ltrim(' ', to_char($2,'999999999D99'))", but that yields yet anoter error:  "ERROR:  parser: parse error at or near "'"".

Any help 'preciated.  Thanks!