Thread: Problems with using function input paramaters

I have been chasing a bug for a bit now. I even wound up completely rewriting the
function, which in the end turns out to be a good thing, as it is much cleaner and
easy to read. 

I now believe that the bug is in how I am using an input parameter to the
function. Here is the function deceleration:

CREATE FUNCTION
return_previous_month_start_and_end(
integer)
RETURNS interval_dates AS $$

Here is one of the places I am using it:


my_year :=      ( select
                 cast(extract(year from
                            cast(date_trunc('month',
                                CURRENT_DATE) - interval '  $1
                                month - 1 ' day as date)
                                ) as integer)
                        ) ;
This prints the correct value BTW:

 RAISE notice 'Called with %', $1 ;

 Nay thoughts as to what I am doing wrong?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On Sat, Aug 31, 2019 at 10:05:10AM -0400, stan wrote:
> I have been chasing a bug for a bit now. I even wound up completely rewriting the
> function, which in the end turns out to be a good thing, as it is much cleaner and
> easy to read. 
> 
> I now believe that the bug is in how I am using an input parameter to the
> function. Here is the function deceleration:
> 
> CREATE FUNCTION
> return_previous_month_start_and_end(
> integer)
> RETURNS interval_dates AS $$
> 
> Here is one of the places I am using it:
> 
> 
> my_year :=      ( select
>                  cast(extract(year from
>                             cast(date_trunc('month',
>                                 CURRENT_DATE) - interval '  $1
>                                 month - 1 ' day as date)
>                                 ) as integer)
>                         ) ;
> This prints the correct value BTW:
> 
>  RAISE notice 'Called with %', $1 ;
> 
>  Nay thoughts as to what I am doing wrong?
> 
BTW, this article
https://dba.stackexchange.com/questions/159424/how-to-use-function-parameters-in-dynamic-sql-with-execute
seems to imply that I can use the $1 syntax in a string, but others refer to using
the COALESCE functionality, but I am uncertain exactly how to use this here.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sat, Aug 31, 2019 at 7:05 AM stan <stanb@panix.com> wrote:
>> CURRENT_DATE) - interval '  $1
>> month - 1
>> ' day as date)

> ($1 || ' month')::interval  -- should work

FWIW, I tend to prefer doing it with interval arithmetic, like this:

$1 * '1 month'::interval - '1 day'::interval

It's faster (not enormously so, but measurably) thanks to not having
to construct and then parse a text string.  To my mind it's easier
to reason about, too, and a bit safer.

            regards, tom lane