pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Can't convert numeric to_char() in catenate function
3+ messages / 2 participants
[nested] [flat]

* Can't convert numeric to_char() in catenate function
@ 2003-10-16 22:24  Yusuf <yusufad@myrealbox.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Yusuf @ 2003-10-16 22:24 UTC (permalink / raw)
  To: pgsql-sql


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!



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Can't convert numeric to_char() in catenate function
@ 2003-10-16 23:04  Josh Berkus <josh@agliodbs.com>
  parent: Yusuf <yusufad@myrealbox.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2003-10-16 23:04 UTC (permalink / raw)
  To: Yusuf <yusufad@myrealbox.com>; pgsql-sql

Yusuf,

> 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')

You need to escape your single quotes by double-quoting them, eg.:
 to_char($2,''99999999D99'')

-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Can't convert numeric to_char() in catenate function
@ 2003-10-16 23:26  Yusuf <yusufad@myrealbox.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Yusuf @ 2003-10-16 23:26 UTC (permalink / raw)
  To: pgsql-sql

I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. 

DANG!!

I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. 

Err, sorry about that, I'm trying to keep something in my mind.  I just wanted to tell ya to forget my previous mail, I got it (tipical) just after sending it:

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 ltrim('' '', to_char($2,''999999999D99''))
            ELSE $1 || '', '' || ltrim('' '',  to_char($2,''99999999D99''))
       END
' LANGUAGE 'sql';

Ciao.

I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes...




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-10-16 23:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-16 22:24 Can't convert numeric to_char() in catenate function Yusuf <yusufad@myrealbox.com>
2003-10-16 23:04 ` Josh Berkus <josh@agliodbs.com>
2003-10-16 23:26 Re: Can't convert numeric to_char() in catenate function Yusuf <yusufad@myrealbox.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox