Обсуждение: explicit casts

Поиск
Список
Период
Сортировка

explicit casts

От
Iuri Sampaio
Дата:
Hi there,

I installed postgresql 8.4 on my box and now i have troubles with the 
following query regarding explicit casts.

select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 
'fmMonth') as month,    to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY') 
as year,    to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as 
first_julian_date_of_month,    to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as 
num_days_in_month,    to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as 
first_day_of_month,    to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as last_day,
trunc(add_months(to_date(:the_date,'yyyy-mm-dd'), 1),'Day') as 
 
next_month,    trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as 
prev_month,    trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date,'yyyy-mm-dd'), -1)), 
 
'DD') as days_in_last_month,    to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1), 'fmMonth') 
as next_month_name,    to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1), 
'fmMonth') as prev_month_name    from dual

the value assigned to the variable :the_date is '2010-01-05'

The error is

Error: Ns_PgExec: result status: 7 message: ERROR:  function 
to_date(timestamp with time zone, unknown) does not exist
LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...               ^
HINT:  No function matches the given name and argument types. You might 
need to add explicit type casts.
QUERY:  select to_date(date_trunc('month',add_months( $1 
,1)),'YYYY-MM-DD') - 1
CONTEXT:  PL/pgSQL function "last_day" line 6 at SQL statement


how would i apply the following solution

date_trunc('month', p_date_in + interval '1 month')::date - 1

to fix the query above?

cheers,
iuri

Re: explicit casts

От
Iuri Sampaio
Дата:
So far,
I could write the following query

select to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 
'fmMonth') as month,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'YYYY') as 
year,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'J') as 
first_julian_date_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'D') as 
first_day_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as last_day,
trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1),'Day') as 
next_month,
trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1),'Day') as 
prev_month,
trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as 
days_in_last_month,
to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1), 'fmMonth') 
as next_month_name,
to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1), 'fmMonth') 
as prev_month_name
from dual


But i still miss some lines in order to properly explicit casts in the query

cheers,
iuri

On 01/05/2011 10:24 PM, Iuri Sampaio wrote:
> Hi there,
>
> I installed postgresql 8.4 on my box and now i have troubles with the 
> following query regarding explicit casts.
>
> select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 
> 'fmMonth') as month,
>     to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY') 
> as year,
>     to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as 
> first_julian_date_of_month,
>     to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as 
> num_days_in_month,
>     to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as 
> first_day_of_month,
>     to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as 
> last_day,
>     trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),'Day') as 
> next_month,
>     trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as 
> prev_month,
>     trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
>     to_char(last_day(add_months(to_date(:the_date, 'yyyy-mm-dd'), 
> -1)), 'DD') as days_in_last_month,
>     to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1), 
> 'fmMonth') as next_month_name,
>     to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1), 
> 'fmMonth') as prev_month_name
>     from dual
>
> the value assigned to the variable :the_date is '2010-01-05'
>
> The error is
>
> Error: Ns_PgExec: result status: 7 message: ERROR:  function 
> to_date(timestamp with time zone, unknown) does not exist
> LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...
>                ^
> HINT:  No function matches the given name and argument types. You 
> might need to add explicit type casts.
> QUERY:  select to_date(date_trunc('month',add_months( $1 
> ,1)),'YYYY-MM-DD') - 1
> CONTEXT:  PL/pgSQL function "last_day" line 6 at SQL statement
>
>
> how would i apply the following solution
>
> date_trunc('month', p_date_in + interval '1 month')::date - 1
>
> to fix the query above?
>
> cheers,
> iuri

Re: explicit casts

От
Adrian Klaver
Дата:
On Wednesday 05 January 2011 4:24:34 pm Iuri Sampaio wrote:
> Hi there,
>
> I installed postgresql 8.4 on my box and now i have troubles with the
> following query regarding explicit casts.
>
> select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'),
> 'fmMonth') as month,
>      to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY')
> as year,
>      to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
>      to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as
> num_days_in_month,
>      to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
>      to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as last_day,
>      trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),'Day') as
> next_month,
>      trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as
> prev_month,
>      trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
>      to_char(last_day(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1)),
> 'DD') as days_in_last_month,
>      to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1), 'fmMonth')
> as next_month_name,
>      to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),
> 'fmMonth') as prev_month_name
>      from dual
>
> the value assigned to the variable :the_date is '2010-01-05'
>
> The error is
>
> Error: Ns_PgExec: result status: 7 message: ERROR:  function
> to_date(timestamp with time zone, unknown) does not exist
> LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...
>                 ^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
> QUERY:  select to_date(date_trunc('month',add_months( $1
> ,1)),'YYYY-MM-DD') - 1
> CONTEXT:  PL/pgSQL function "last_day" line 6 at SQL statement
>
>
> how would i apply the following solution
>
> date_trunc('month', p_date_in + interval '1 month')::date - 1
>
> to fix the query above?
>
> cheers,
> iuri

If I am following this right the problem is in the last_day function and in 
particular the return value of the add_months function used in the 
date_trunc(). To be sure we would need to see those functions. As a shot in the 
dark:select to_date(date_trunc('month',add_months( $1 ,1)::date)...

-- 
Adrian Klaver
adrian.klaver@gmail.com

Re: explicit casts

От
Adrian Klaver
Дата:
On Wednesday 05 January 2011 5:22:30 pm Iuri Sampaio wrote:
> So far,
> I could write the following query
>
> select to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'),
> 'fmMonth') as month,
> to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'YYYY') as
> year,
> to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'J') as
> first_julian_date_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
> to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'D') as
> first_day_of_month,
> to_char(last_day('2010-01-02')::date, 'DD') as last_day,
> trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1),'Day') as
> next_month,
> trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1),'Day') as
> prev_month,
> trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'year') as beginning_of_year,
> to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
> days_in_last_month,
> to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1), 'fmMonth')
> as next_month_name,
> to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1), 'fmMonth')
> as prev_month_name
> from dual
>
>
> But i still miss some lines in order to properly explicit casts in the
> query
>
> cheers,
> iuri


My guess is you upgraded from a version prior to 8.3. In 8.3 many of the implied 
casts where removed, so you probably have to go over your code and make the 
corrections.
Go here for more detail:
http://www.postgresql.org/docs/8.4/interactive/release-8-3.html
SectionE.21.2.1. General

A temporary solution can be found here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

An important tip from the blog-
"The gist was, only restore the casts you need, not all of them."

-- 
Adrian Klaver
adrian.klaver@gmail.com