Search code examples
sqlpostgresqlstored-proceduresparameter-passingplpgsql

Pass parameters to view (set returning function?)

I basically have a pretty complicated view that currently returns what I want from last-week aggregations.

SELECT *
FROM ...
WHERE  t1.event_date >= ('now'::text::date - 7) 
...

Now I'd like to be able to make the same calculations for any given 2 dates, so I want to be able to pass parameters to my view. Pretty much I want to replace the WHERE clause with something like:

WHERE  t1.event_date BETWEEN %first date% AND %second_date%

I think I should be able to do this with a set returning function but can't figure exactly how to do it. Any ideas?

Solution

  • Create a function (sometimes called table-function, when returning a set of rows). There are many (procedural) languages, in particular PL/pgSQL. In your case LANGUAGE sql does the job:

    CREATE OR REPLACE FUNCTION get_user_by_username(d1 date, d2 date)
  RETURNS TABLE ( ... )
  LANGUAGE sql AS
$func$
SELECT ...
WHERE  t1.event_date >= $1
AND    t1.event_date <  $2  -- note how I exclude upper border
$func$;

    Example for LANGUAGE sql:

    Examples for LANGUAGE plpgsql: