Re: How to convert integer to string in functions

Hi,

2011/8/12 David Johnston <polobo(at)yahoo(dot)com>:
> In my table, some of the columns are in text datatype. Few data will come
> down from UI layer as integers. I want to convert that to string/text before
> saving it into the table. Please help me on this.
>
>
> SQL Standard:  "CAST( value AS text )" [or varchar]
> PostgreSQL short-hand:  "value::text"
>
> In both formats replace value with whatever you want to convert.  When
> writing a parameterized  query (using ?) you can write "?::text" ( or Cast(?
> AS type) ) to explicitly cast the unknown parameter.  The "text" in the
> above can be any type name.
>
> David J.

You often find this advice of doing a cast.
But this only works if the input is a clean list of number characters already!
Anything other than this will issue an error:

postgres=# SELECT '10'::int;

After trying hard to cope with anything possibly as an input string I
found this:

postgres=# SELECT to_number('0'||mytextcolumn,
'99999999999.000')::int FROM mytable;

You can try this here: Show all peaks of Switzerland which are higher
than 4000 meters above sea.

SELECT ST_AsText(way) AS geom, name||','||ele AS label
FROM osm_point
WHERE "natural" = 'peak'
AND to_number('0'||ele, '99999999999.000')::int >= 4000

Any better solutions are welcome.

Yours, Stefan