sql - How to pass a parameter into a date function -

i trying create simple function , cannot seem pass in parameter date function.
here function:

create or replace function test(source int,days text) returns integer $totals$ declare     totals integer; begin     select             count(id) totals       ad            createdate::date = date(current_date - interval '$2' day) ,        source = $1;     return totals;  end; $totals$ language plpgsql; 

@imsop shed light upon syntax error. however, can simpler, faster , cleaner in multiple ways.

create or replace function test(_source int, _days int)   returns integer $func$ select count(*)::int   ad  a.source = $1 ,    a.createdate::date = current_date - $2 $func$ language sql stable; 

• first of all, subtract days date, can can subtract integer number. accordingly use integer parameter here.

  • how determine last day of previous month using postgresql?

• you don't need plpgsql simple function this. use sql function instead - can "inlined" in context of bigger query, , optimized better in cases.

• the function can stable (which allows inlining mentioned above).

  • how immutable, stable , volatile keywords effect behaviour of function?

• you had naming conflict built function. source column name. try avoid that. common practice prepend variables , parameters underscore (which has no special meaning otherwise). can table-qualify column names and/or prepend parameter names function name (or use positional parameters) unambiguous. did both here.

• assuming id pk column , defined not null, count(*) same count(id), bit shorter , cheaper. cast integer, because count() return bigint.

however, going out on limb here, suspect inaccurately named column createdate not date timestamp (essential table definition missing in question). in case it's more efficient phrase query differently:

create or replace function test(_source int, _days int)   returns integer $func$ select count(*)::int   ad  a.source = $1 ,    a.createdate >= now() - interval '1 day' * $2 ,    a.createdate <  now() - interval '1 day' * ($2 - 1) $func$ language sql stable; 

• this expression sargable , more efficient. can use plain index on (createdate), or better on (source, createdate) - important big tables.

• also demonstrating alternative way subtract days. can multiply interval '1 day'. related:

  • query using 2 column values create range