cast problem in Postgresql 9.0.1
От | AI Rumman |
---|---|
Тема | cast problem in Postgresql 9.0.1 |
Дата | |
Msg-id | AANLkTi=yc+NcCcy=_S42eLCn79vOZawbEJMa=Pe57gvb@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: cast problem in Postgresql 9.0.1
Re: cast problem in Postgresql 9.0.1 Re: cast problem in Postgresql 9.0.1 |
Список | pgsql-general |
I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1.
I have a table "testtab"
\d testtab
id int,
hours varchar
When I execute the following:
select sum(hours) from testtab
I get cast error.
Then,
I created following IMPLICIT CAST functions in my DB =>
CREATE FUNCTION pg_catalog.integer(varchar) RETURNS int4 STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT int4in(varcharout($1));';
CREATE CAST (varchar AS integer) WITH FUNCTION pg_catalog.integer(varchar) AS IMPLICIT;
CREATE FUNCTION pg_catalog.smallint(varchar) RETURNS smallint STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT int2in(varcharout($1));';
CREATE CAST (varchar AS smallint) WITH FUNCTION pg_catalog.smallint(varchar) AS IMPLICIT;
Now, the above query works, but
SELECT COALESCE(hours,0) from testtab
failed.
Any idea why?
I have a table "testtab"
\d testtab
id int,
hours varchar
When I execute the following:
select sum(hours) from testtab
I get cast error.
Then,
I created following IMPLICIT CAST functions in my DB =>
CREATE FUNCTION pg_catalog.integer(varchar) RETURNS int4 STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT int4in(varcharout($1));';
CREATE CAST (varchar AS integer) WITH FUNCTION pg_catalog.integer(varchar) AS IMPLICIT;
CREATE FUNCTION pg_catalog.smallint(varchar) RETURNS smallint STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT int2in(varcharout($1));';
CREATE CAST (varchar AS smallint) WITH FUNCTION pg_catalog.smallint(varchar) AS IMPLICIT;
Now, the above query works, but
SELECT COALESCE(hours,0) from testtab
failed.
Any idea why?
В списке pgsql-general по дате отправления: