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);
Unfortunately, mysql_config
contains quite a bit of black magic and quite a bit of blatant sketchiness. It's notoriously untrustworthy if you have more than one copy of it or the version doesn't match your server build.
Unfortunately, too, if you're using one from a distro (not official binaries) then that utility may not even be in the same package as the server... it's bundled with the client, which could easily be 32 bit and work just fine with a 64-bit server.
$ mysql_config
The program 'mysql_config' can be found in the following packages:
* libmysqlclient-dev
* libmariadbclient-dev
Try: sudo apt-get install <selected package>
Not confidence-inspiring.
$ mysql_config --version
5.5.40
Neat, I don't even have server on this particular system, and never have.
So, that gives you a sense of how much hocus pocus this little utility can sometimes offer up.
Fundamentally, though, there's this: wrong ELF class: ELFCLASS32
means you compiled the plugin for 32 bit, but your server is indeed 64 bit.
The missing piece from your compiler flags is -m64
. If you add that and remove the -m32
(I'm not sure where that's coming from... mysql_config
doesn't include either one on my test system) then you should be able to get that plugin to compile and install.
You have the plugin in the correct directory, or the system wouldn't have found the file to complain about the architecture it was compiled for.
Best Answer
Collations are database objects, so their name is an identifier.
You cannot use a parameter for an identifier, you'd have to use dynamic SQL in a PL/pgSQL function: