Re: Function Help

Brian C. Doyle writes:

> 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.

Don't quote the $1.  E.g.,

WHEN date_part(\'dow\',$1)=6 THEN date($1)+0

The $1 etc. are not macros, they are identifiers representing a typed
expression.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter