Problems with using function input paramaters

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

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

Content within a literal (i.e., between single quotes) is always treated as
literal content.

David J.

"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