Postgres Pro
Downloads
Home   >   mailing lists

Thread: argh... can't cast varchar to int

argh... can't cast varchar to int

From
Matthew Nuzum
Date:
I have a table that has (among other things) two values, one named
ftr_type and the other ftr_data.

Depending on the value of ftr_type, ftr_data may contain the value of a
primary key in another table.  Because sometimes there is another table
and sometimes there isn't, ftr_data is a varchar.

I'm writing some SQL functions that will help me maintain referential
integrity, but they won't let me use the value in ftr_data as a
reference to the other table.  It says:

Cannot cast type 'character varying' to 'integer'

Here's what I've tried:
select CAST(ftr_data as int4) from pages where pageid = 2783;
ERROR:  Cannot cast type 'character varying' to 'integer'

select ftr_data::int4 from pages where pageid = 2783;
ERROR:  Cannot cast type 'character varying' to 'integer'

select to_num(ftr_data) from pages where pageid = 2783;
ERROR:  Function 'to_num(varchar)' does not exist
        Unable to identify a function that satisfies the given argument
types
        You may need to add explicit typecasts

Just for reference, here's what the data in ftr_data looks like in this
case:
select ftr_data from pages where pageid = 2783;
 ftr_data
----------
 22

Also for reference, this doesn't work:
select CAST('22'::varchar as int4);
ERROR:  Cannot cast type 'character varying' to 'integer'

Can anyone suggest a way that I can do this in my pl/pgsql function?
Thanks,
Matthew Nuzum

Re: argh... can't cast varchar to int

From
Oliver Elphick
Date:
On Mon, 2002-11-25 at 02:28, Matthew Nuzum wrote:
> I have a table that has (among other things) two values, one named
> ftr_type and the other ftr_data.
>
> Depending on the value of ftr_type, ftr_data may contain the value of a
> primary key in another table.  Because sometimes there is another table
> and sometimes there isn't, ftr_data is a varchar.
>
Why not make it integer but leave its value as NULL if there is no other
table?

Alternatively, cast it to text first:

        junk=# select CAST('22'::text as int4);
         int4
        ------
           22
        (1 row)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Let us therefore come boldly unto the throne of grace,
      that we may obtain mercy, and find grace to help in
      time of need."             Hebrews 4:16