Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can't convert numeric to_char() in catenate function

Can't convert numeric to_char() in catenate function

From
Yusuf
Date:
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!

Re: Can't convert numeric to_char() in catenate function

From
Josh Berkus
Date:
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 BerkusAglio Database SolutionsSan Francisco

Re: Can't convert numeric to_char() in catenate function

From
"Yusuf"
Date:
I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I 'must'
quotethe 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
gotit (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...