Troubleshooting a CASE query in PostgreSQL

It looks like you're keeping time-based object instances in the statut_existenta_pf table.

I've used the following testbed:

CREATE TABLE statut_existenta_pf (
    se_id                   int4,
    se_id_pf                int4,
    se_id_statut            int2,
    se_data_inceput         date,
    se_data_sfarsit         date
); -- the rest fields are irrelevant for this example
CREATE TABLE nom_statut_existenta (
    id           int2,
    ne_denumire  varchar(30)
); -- my wild guess bout this table
CREATE TABLE persoane_fizice (
    pf_id           int4,
    pf_name         varchar(60)
); --- same here, irrelevant for the example

with the following test data:

INSERT INTO nom_statut_existenta VALUES
    (1, 'Status: Vive'), (2, 'Status: Morto');
INSERT INTO persoane_fizice VALUES (3489, 'Giuseppe Garibaldi');
INSERT INTO statut_existenta_pf VALUES
    (4275, 3489, 2, '2012-04-18', '2012-05-18'),
    (3669, 3489, 1, '2010-03-31', NULL);

Now, you're searching for the current object in this time-based series. Your logic seems to be the following:

1. if se_data_inceput is NULL (I treat this column as instance_start_date), then get the value of the most recent revision for the given persoane_fizice;
2. if se_data_sfarsit is NULL (instance_end_date I believe) then again, get the value of the most recent revision;
3. if both columns are NOT NULL, then get the value for the revision falling between such dates.

You've mentioned nothing bout the constraints in your setup, but I assume it is illegal to have several entries with overlapping date ranges.

Here's how I rewrote your initial query, yielding proper results:

WITH max_se_id AS (
SELECT se_id_pf, max(se_id) se_id_max FROM statut_existenta_pf
 GROUP BY se_id_pf
)
SELECT p.*, se.se_id_statut, ne.ne_denumire
  FROM persoane_fizice p
  JOIN statut_existenta_pf se ON se.se_id_pf = p.pf_id
  JOIN nom_statut_existenta ne ON ne.id = se.se_id_statut
  JOIN max_se_id mse ON se.se_id_pf = mse.se_id_pf
 WHERE p.pf_id = :id_pf
   AND se.se_id_statut IN 
    (CASE 
       WHEN se_data_inceput IS NULL THEN mse.se_id_max
       WHEN se_data_inceput IS NOT NULL AND se_data_sfarsit IS NULL THEN mse.se_id_max
       ELSE se_id_statut
    END) ;

But this query yields incorrect results, for the given testbed it will return the revision with the highest se_id, despite the fact it has start time in the future.

I'm using the same approach to keep history of objects in the database and I recommend using such query instead:

SELECT p.*, se.se_id_statut, ne.ne_denumire
  FROM persoane_fizice p
  JOIN statut_existenta_pf se ON se.se_id_pf = p.pf_id
  JOIN nom_statut_existenta ne ON ne.id = se.se_id_statut
 WHERE p.pf_id = :id_pf
   AND statement_timestamp() BETWEEN coalesce(se.se_data_inceput, now())
       AND coalesce(se.se_data_sfarsit, clock_timestamp());

If you have non-overlapping dates in the se_data_inceput + se_data_sfarsit columns, this query will yield the currently active row.

I'm using:

• now() as a default value for the se_data_inceput, this yields the start time of current transaction;
• statement_timestamp() as a current point in time and
• clock_timestamp() as a default for the se_data_sfarsit.

With this combination you can always expect your query to return the current object instance from the history table.

I hope my assumptions were right.

I'm not sure CASE can return a list, which you are getting in the ELSE part, maybe someone else can enlighten us. In the meantime, I think you can put the se.se_id_statut IN part inside the CASE:

AND 
    (CASE 
       WHEN se_data_inceput IS NULL THEN
         se.se_id_statut IN (SELECT se_id_statut FROM statut_existenta_pf
            WHERE se_id_pf = :id_pf
            ORDER BY se_id DESC LIMIT 1)
       WHEN se_data_inceput IS NOT NULL AND se_data_sfarsit IS NULL THEN
         se.se_id_statut IN (SELECT se_id_statut FROM statut_existenta_pf
            WHERE se_id_pf = :id_pf
            ORDER BY se_id DESC LIMIT 1)
       ELSE
         se.se_id_statut IN  (SELECT se_id_statut FROM statut_existenta_pf
            WHERE se_id_pf = :id_pf
              AND CURRENT_DATE BETWEEN se_data_inceput AND se_data_sfarsit)
    END)