I am trying to create a simple function and I cannot seem to pass in a parameter into the date function.
Here is the function:
CREATE OR REPLACE FUNCTION test(source int,days text)
RETURNS integer AS $totals$
declare
totals integer;
BEGIN
select
count(id) into totals
from ad
where
createdate::date = date(current_date - interval '$2' day) and
source = $1;
RETURN totals;
END;
$totals$ LANGUAGE plpgsql;
@IMSoP already shed light upon your syntax error. However, this can be simpler, faster and cleaner in multiple ways.
First of all, to subtract days from a
date
, you can can just subtract aninteger
number. Accordingly I use aninteger
parameter here.You don't need plpgsql for a simple function like this. Use an SQL function instead - which can be "inlined" in the context of a bigger query, and thus optimized better in certain cases.
The function can be
STABLE
:You had a naming conflict built into your function.
source
is obviously also a column name. Try to avoid that. A common practice is to prepend variables and parameters with an underscore (which has no special meaning otherwise). You can also table-qualify column names and/or prepend parameter names with the function name (or use positional parameters) to be unambiguous. I did both here.Assuming
id
is your PK column and thus definedNOT NULL
,count(*)
does the same ascount(id)
, a bit shorter and cheaper. I cast tointeger
, because count() will return abigint
.However, going out on a limb here, I suspect your inaccurately named column
createdate
is not actually adate
but atimestamp
(essential table definition is missing in the question). In that case it's much more efficient to phrase the query differently:This expression is sargable and thus more efficient. It can also use a plain index on
(createdate)
, or better on(source, createdate)
- important for big tables.Also demonstrating an alternative way to subtract days. You can multiply the
interval '1 day'
. Related: