LEFT JOIN条件为变量

Question

我正在创建PostgreSQL视图。(部分问题在这里得到了解决PostgreSQL-如果数据为空(当使用where子句时)，则将其计数为零)：

我遇到的问题是，我需要使用LEFT JOIN中的条件作为变量，因为我不知道在该查询(视图)的"运行时"之前将使用哪种日期进行筛选。这种视图在OpenERP中使用。它使用字段来筛选SELECT主列中的结果。例如，当在视图中看到opp.name as name时，在OpenERP中可以使用此列进行筛选。这种方法是可以的，但我需要看到所有LEFT表的结果。我的意思是，若我使用任何过滤器，它仍然应该显示右表中的零(null)值，如下例所示。但在OpenERP中，如果您使用SELECT中使用的来自列的筛选器，那么它将作为普通JOIN进行筛选，结果将仅为两列中都存在的值(或者不为LEFT JOIN)。

那么，是否有某种方法来描述变量，而不是在LEFT JOIN中使用特定日期作为条件(现在在示例中，您看到了这个AND log.create_date <= '2014-01-29 08:49:03'，我需要类似AND log.create_date <= some_variable的东西)。以下代码：

CREATE TABLE crm_lead(
id integer PRIMARY KEY,
name char(64) NOT NULL
);
CREATE TABLE crm_lead_stage_log(
id integer PRIMARY KEY,
opportunity_id integer references crm_lead(id),
stage_id integer,
create_date timestamp
);
INSERT INTO crm_lead VALUES (1, 'opp1');
INSERT INTO crm_lead VALUES (2, 'opp2');
INSERT INTO crm_lead VALUES (3, 'opp3');
INSERT INTO crm_lead_stage_log VALUES (1, 1, 1, '2014-01-28 08:49:03');
INSERT INTO crm_lead_stage_log VALUES (2, 1, 2, '2014-01-29 08:49:03');
INSERT INTO crm_lead_stage_log VALUES (3, 2, 1, '2014-02-02 08:49:03');
INSERT INTO crm_lead_stage_log VALUES (4, 2, 1, '2014-02-03 08:49:03');
CREATE VIEW report_crm_lead_stage_log as
SELECT opp.name as name,
count(log.stage_id) as stage_count,
to_char(log.create_date, 'YYYY-MM-DD') as stage_changed_day
FROM crm_lead as opp 
LEFT JOIN crm_lead_stage_log as log 
ON opp.id = log.opportunity_id
AND log.create_date <= '2014-01-29 08:49:03'
GROUP BY name, log.create_date
ORDER BY name;

输出是这样的：

NAME            STAGE_COUNT STAGE_CHANGED_DAY
opp1    1           2014-01-28
opp1            1           2014-01-29
opp2            0           (null)
opp3            0           (null)

你也可以在这里查看架构(http://sqlfiddle.com/#！12/de78e/1)

Answer 1

下面是一个函数示例：

--Creating a type returned by the function:
CREATE TYPE my_type AS (name CHARACTER(64), stage_count BIGINT, stage_changed_day TEXT);
--And the function
CREATE OR REPLACE FUNCTION report_crm_lead_stage_log(date_filter timestamp without time zone)
RETURNS SETOF my_type AS
$BODY$declare 
begin
return query (SELECT opp.name as name,
count(log.stage_id) as stage_count,
to_char(log.create_date, 'YYYY-MM-DD') as stage_changed_day
FROM crm_lead as opp 
LEFT JOIN crm_lead_stage_log as log 
ON opp.id = log.opportunity_id
AND log.create_date <= date_filter
GROUP BY name, log.create_date
ORDER BY name);
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

源