Обсуждение: Function Help

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

Function Help

От
"Brian C. Doyle"
Дата:
Hello all,

I am working on a function to determine the date of the first saturday of
the month.

Currently I have:

CREATE FUNCTION first_saturday(date)
RETURNS date
AS '
Select CASE WHEN date_part(\'dow\',\'$1\'::DATE)=0 THEN date(\'$1\')+6
WHEN date_part(\'dow\',\'$1\'::DATE)=1 THEN date(\'$1\')+5
WHEN date_part(\'dow\',\'$1\'::DATE)=2 THEN date(\'$1\')+4
WHEN date_part(\'dow\',\'$1\'::DATE)=3 THEN date(\'$1\')+3
WHEN date_part(\'dow\',\'$1\'::DATE)=4 THEN date(\'$1\')+2
WHEN date_part(\'dow\',\'$1\'::DATE)=5 THEN date(\'$1\')+1
WHEN date_part(\'dow\',\'$1\'::DATE)=6 THEN date(\'$1\')+0
END'LANGUAGE 'sql'

I get an error that $1 is not a valid date.  But I want that to be the
variable I enter...
what Am I doing wrong???




Brian C. Doyle
Supervisor: Web Link
Earthlink Web Hosting Technical Support
webhelp@mindspring.net

Re: Function Help

От
Masaru Sugawara
Дата:
Brian C. Doyle <doyleb@corp.earthlink.net> wrote:

> I am working on a function to determine the date of the first saturday of
> the month.
>
> Currently I have:
>
> CREATE FUNCTION first_saturday(date)
> RETURNS date
> AS '
> Select CASE WHEN date_part(\'dow\',\'$1\'::DATE)=0 THEN date(\'$1\')+6
> WHEN date_part(\'dow\',\'$1\'::DATE)=1 THEN date(\'$1\')+5
> WHEN date_part(\'dow\',\'$1\'::DATE)=2 THEN date(\'$1\')+4
> WHEN date_part(\'dow\',\'$1\'::DATE)=3 THEN date(\'$1\')+3
> WHEN date_part(\'dow\',\'$1\'::DATE)=4 THEN date(\'$1\')+2
> WHEN date_part(\'dow\',\'$1\'::DATE)=5 THEN date(\'$1\')+1
> WHEN date_part(\'dow\',\'$1\'::DATE)=6 THEN date(\'$1\')+0
> END'LANGUAGE 'sql'
>
> I get an error that $1 is not a valid date.  But I want that to be the
> variable I enter...
> what Am I doing wrong???


 Hi,Brian.

 It doesn't need the meta character \' that you write on both sides
 of $1, so you get the error.
 By the way, I think the following query is one of what you want.


CREATE FUNCTION first_saturday(date)
RETURNS date
AS '
  SELECT (month.firstday
          + ((6 - date_part(''dow'', month.firstday))::text || '' day''
             )::interval
          )::date
     FROM (select ($1::timestamp
                   - ((date_part(''day'', $1::timestamp)-1)::text
                         || '' day''
                      )::interval
                   ) AS firstday
          ) AS month
' LANGUAGE 'sql'
;


ex.

# select first_saturday('2001/9/28'::date);
 first_saturday
----------------
 2001-09-01

# select first_saturday('2001/10/28'::date);
 first_saturday
----------------
 2001-10-06

# select first_saturday('1999/9/28'::date);
 first_saturday
----------------
 1999-09-04

# select first_saturday('2010/9/28'::date);
 first_saturday
----------------
 2010-09-04



Regards.

------------------
 Masaru Sugawara
rk73@echna.ne.jp

Re: Function Help

От
Masaru Sugawara
Дата:
Brian C. Doyle <doyleb@corp.earthlink.net> wrote:

> I am working on a function to determine the date of the first saturday of
> the month.
>
> Currently I have:
>
> CREATE FUNCTION first_saturday(date)
> RETURNS date
> AS '
> Select CASE WHEN date_part(\'dow\',\'$1\'::DATE)=0 THEN date(\'$1\')+6
> WHEN date_part(\'dow\',\'$1\'::DATE)=1 THEN date(\'$1\')+5
> WHEN date_part(\'dow\',\'$1\'::DATE)=2 THEN date(\'$1\')+4
> WHEN date_part(\'dow\',\'$1\'::DATE)=3 THEN date(\'$1\')+3
> WHEN date_part(\'dow\',\'$1\'::DATE)=4 THEN date(\'$1\')+2
> WHEN date_part(\'dow\',\'$1\'::DATE)=5 THEN date(\'$1\')+1
> WHEN date_part(\'dow\',\'$1\'::DATE)=6 THEN date(\'$1\')+0
> END'LANGUAGE 'sql'
>
> I get an error that $1 is not a valid date.  But I want that to be the
> variable I enter...
> what Am I doing wrong???


 Hi,Brian.

 It doesn't need the meta character \' that you write on both sides
 of $1, so you get the error.
 By the way, I think the following query is one of what you want.


CREATE FUNCTION first_saturday(date)
RETURNS date
AS '
  SELECT (month.firstday
          + ((6 - date_part(''dow'', month.firstday))::text || '' day''
             )::interval
          )::date
     FROM (select ($1::timestamp
                   - ((date_part(''day'', $1::timestamp)-1)::text
                         || '' day''
                      )::interval
                   ) AS firstday
          ) AS month
' LANGUAGE 'sql'
;


ex.

# select first_saturday('2001/9/28'::date);
 first_saturday
----------------
 2001-09-01

# select first_saturday('2001/10/28'::date);
 first_saturday
----------------
 2001-10-06

# select first_saturday('1999/9/28'::date);
 first_saturday
----------------
 1999-09-04

# select first_saturday('2010/9/28'::date);
 first_saturday
----------------
 2010-09-04



Regards.

------------------
 Masaru Sugawara
rk73@echna.ne.jp