[SQL] 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, '-mm-dd'), 'Month'), 
'fmMonth') as month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '') 
as year,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as 
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as 
num_days_in_month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as 
first_day_of_month,

to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as last_day,
trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as 
next_month,
trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as 
prev_month,

trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, '-mm-dd'), -1)), 
'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, '-mm-dd'), 1), 'fmMonth') 
as next_month_name,
to_char(add_months(to_date(:the_date, '-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)),'-...
   ^
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)),'-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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] explicit casts

[Iuri Sampaio]

So far,
I could write the following query

select to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 
'fmMonth') as month,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), '') as 
year,
to_char(trunc(to_date('2010-01-02', '-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', '-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', '-mm-dd'), 1),'Day') as 
next_month,
trunc(add_months(to_date('2010-01-02', '-mm-dd'), -1),'Day') as 
prev_month,

trunc(to_date('2010-01-02', '-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', '-mm-dd'), 1), 'fmMonth') 
as next_month_name,
to_char(add_months(to_date('2010-01-02', '-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, '-mm-dd'), 'Month'), 
'fmMonth') as month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '') 
as year,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as 
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as 
num_days_in_month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as 
first_day_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as 
last_day,
trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as 
next_month,
trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as 
prev_month,

trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, '-mm-dd'), 
-1)), 'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, '-mm-dd'), 1), 
'fmMonth') as next_month_name,
to_char(add_months(to_date(:the_date, '-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)),'-...
   ^
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)),'-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



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] sql query - create replace function

[Iuri Sampaio]

Hi all,

I created a script to install postgresql. One of the steps is to run a sql
query.

the problem is that i need to run the query as postgres user, plus it needs
to be at psql shell prompt command line, i.e:

# from your shell prompt enter:
$ psql 

# From the psql prompt enter the follow plpgsql code to create the
bitfromint4 function:

$ create or replace function bitfromint4 (integer) returns bit varying as '
begin return $1::bit(32); end;' language 'plpgsql' immutable strict;

#Exit psql:
$ \q



I expected something like
su - postgres -c "pgsql dbname && create or replace function bitfromint4
(integer) returns bit varying as ' begin return $1::bit(32); end;' language
'plpgsql' immutable str\
ict;"

but i see when the script runs "pgsql dbname" the bash script looses control
of the process, and there's no way back, from pgql shell prompt when it gets
in, to continue the script

iuri