explicit casts

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

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

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(dot)klaver(at)gmail(dot)com

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
Section
E.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(dot)klaver(at)gmail(dot)com