Pass parameters to view (set returning function?)

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-06-05 23:15:45Z

1

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:

Return rows from a PL/pgSQL function

Examples for LANGUAGE plpgsql:

PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

edited Jun 5, 2023 at 23:15

answered Oct 20, 2013 at 22:24

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pass parameters to view (set returning function?)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related