LEFT JOIN with condition that is variable

Question

I'm creating PostgreSQL view. (part of the problem was solved here PostgreSQL - count data as zero if it is null (when where clause is used)):

The problem I'm having is that I need to use condition in LEFT JOIN as variable as I don't know what kind of date will be used to filter before 'runtime' of that query (view). This kind of view is used in OpenERP. It uses fields to filter results from main SELECT columns. For example when in view you see opp.name as name, in OpenERP you can filter using this column. This kind of approach would be OK, but I need to see all LEFT table results. What I mean is if I use any filter, it should still show zero (null) values from right table as in example below. But in OpenERP if you use filter from column that is used in SELECT, then it filters as normal JOIN and result would be only values that exist in both columns (or not LEFT JOIN).

So is there some way to somehow describe variable instead of using specific date as condition in LEFT JOIN (now in example you see this AND log.create_date <= '2014-01-29 08:49:03' and I need something like AND log.create_date <= some_variable). Code below:

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;

The output is this:

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

You can look in schema here too (http://sqlfiddle.com/#!12/de78e/1)

You can achieve this using a function that returns set of record. Is there a possibility to replace your view by a function ? — Houari, Feb 14 '14 at 13:43
@Houari I read it's possible to do that, but it is not encouraged. But if there is not other option, maybe you can post this suggestion so I could check out and try to make it work. — Andrius, Feb 14 '14 at 13:48
possible duplicate of [Pass In "WHERE" parameters to PostgreSQL View?](http://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view) — Andrei Boyanov, Feb 16 '14 at 07:52

score 0 · Answer 1 · answered Feb 14 '14 at 14:14

Here is a function sample:

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

Source

LEFT JOIN with condition that is variable

1 Answers1