Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

share|improve this question
    
As for your additional question: Calling it a couple of times is fine. For a big chart or repeated use, I would create a separate query / function. Depends on circumstances. –  Erwin Brandstetter Jul 14 at 17:04
    
@Erwin Brandstetter: If he wants to work with the data and join it with other results, creating a view might be a better idea? He can still use the view in the function and put indexes on it to speed up queries. –  few Jul 14 at 18:28
    
@few: You cannot "put indexes" on a view. Performance of a view is roughly the same as performance of a function or a plain query. The best strategy depends on exact requirements. –  Erwin Brandstetter Jul 14 at 20:59
    
@ErwinBrandstetter: it takes around 7/8 ms to get the results for one day (and this is for just 3 to 5% of the date that I intent to use this on) plus that I intend to get the data for a range of dates. It will be better to duplicate the data into another table? –  uhn-nohn Jul 15 at 10:36
    
That's really hard to tell. Might be worth another question with all relevant details. –  Erwin Brandstetter Jul 15 at 13:39

1 Answer 1

up vote 6 down vote accepted

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);
share|improve this answer
    
Thank you for the help, its partially working, but when I change the date for yesterday lets say, all the (today, yesterday, week, month) are empty, I know is because of (now()::date), (now()::date - 1), (now()::date - 7),((now() - interval '1 month')::date), and I tried changing now()::date with $1` and with _data also I tried formatting it as I did for the second values, it still didn't work. –  uhn-nohn Jul 14 at 15:14
1  
@uhn-nohn: Did you consider what I posted in the chapter "Tricky detail"? I added complete code examples to clarify. –  Erwin Brandstetter Jul 14 at 16:11
1  
@uhn-nohn: For completeness, I added a CTE version. –  Erwin Brandstetter Jul 14 at 16:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.