How to Pass a Parameter into a Function in PostgreSQL

functionspostgresqlview

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date?

Also is it good practice to call that function multiple times and pass different dates to fill a chart (for example)?

Best Answer

I suggest an SQL function:

CREATE OR REPLACE FUNCTION foo(_date date)
  RETURNS TABLE (
   name       text  -- types have to match your actual types!
 , keyword_id int
 , project_id int
 , the_date   date
 , today      int
 , yesterday  int
 , week       int
 , month      int) AS
$func$

SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col alias irrelevant
     , f.t AS today, f.y As yesterday, f.w AS week, f.m AS month
FROM   crosstab(

    -- crosstab function from previous question here
    -- http://dba.stackexchange.com/a/71266/3684

   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

$func$  LANGUAGE sql;

Call:

SELECT * FROM foo('2014-07-07');

Replace all occurrences of now()::date with your input parameter named _date.
In Postgres 9.1 or older use the positional parameter $1 in SQL functions (which can be used in any version).

More code examples:

Tricky detail

The crosstab() function takes query strings as parameters. Function parameters are not visible inside crosstab(). So you need to pass in the date values as string literals!
I suggest the function format() for convenience. For example, the second parameter becomes:

,format('VALUES(%L::date), (%L), (%L), (%L)'
        , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date
       )

instead of:

,$$
 VALUES
       (now()::date)
     , (now()::date - 1)
     , (now()::date - 7)
     , ((now() - interval '1 month')::date)
 $$

Complete code

SQL function

CREATE OR REPLACE FUNCTION foo_sql(_date date)
  RETURNS TABLE (
   name       text
 , keyword_id int
 , project_id int
 , the_date   date
 , today      int
 , yesterday  int
 , week       int
 , month      int) AS
$func$
BEGIN

SELECT k.name, f.keyword_id, f.project_id, _date
     , f.t, f.y, f.w, f.m
FROM   crosstab (
   $$
   SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0)
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       $$
    || format('VALUES(%L::date), (%L), (%L), (%L)'
              , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date)
   || $$
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at
   $$

  ,format('VALUES(%L::date), (%L), (%L), (%L)'
              , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date)
   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

END
$func$  LANGUAGE sql;

PL/pgSQL function

Shorter, reusing the VALUES expression. Probably a bit faster, too.

CREATE OR REPLACE FUNCTION foo_plpgsql(_date date)
  RETURNS TABLE (name text, keyword_id int, project_id int, the_date date
               , today int, yesterday int, week int, month int) AS
$func$
DECLARE
   _dates text := format('VALUES(%L::date), (%L), (%L), (%L)'
                        , $1, $1 - 1, $1 - 7, ($1 - interval '1 month')::date);
BEGIN

SELECT k.name, f.keyword_id, f.project_id, _date, f.t, f.y, f.w, f.m
FROM   crosstab (
  'SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0)
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       ' || _dates || '
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at'

  ,_dates
   ) f (rn int, keyword_id int, project_id int, t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

END
$func$  LANGUAGE plpgsql;

CTE

For completeness, the "same" without persisting a function, with CTEs:

WITH d(day)   AS (SELECT '2014-07-07'::date)  -- provide your date here
,    v(dates) AS (
   SELECT format('VALUES(%L::date), (%L), (%L), (%L)'
                 , day, day - 1, day - 7
                 ,(day - interval '1 month')::date)
   FROM d
   )
SELECT k.name, f.keyword_id, f.project_id, d.day AS the_date
     , f.t AS today, f.y As yesterday, f.w AS week, f.m AS month
FROM   crosstab (
  'SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0) AS pos
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
       ' || (SELECT dates FROM v) || '
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at'

  ,(SELECT dates FROM v)
   ) f (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);
Related Question