How to pass a parameter into a function

Question

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)?

• Erwin Brandstetter: 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.
• user43739: @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.
• Erwin Brandstetter: @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.
• alexsmn: @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?
• Erwin Brandstetter: That's really hard to tell. Might be worth another question with all relevant details.

Answer 1

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:

• Return rows from a PL/pgSQL function
• Pass In "WHERE" parameters to PostgreSQL View?

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);

• alexsmn: 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.
• Erwin Brandstetter: @uhn-nohn: Did you consider what I posted in the chapter "Tricky detail"? I added complete code examples to clarify.
• Erwin Brandstetter: @uhn-nohn: For completeness, I added a CTE version.