[SQL] Treating result of subselect as row

[Ottó Havasvölgyi]

Hi,

If I have a subselect that returns one row, then how can I use this in
expressions that expects row constructors in the operands, like OVERLAPS or
IN ?

SELECT (subselect1) OVEPLAPS (subselect2)   -- both subselect returns one
row and two columns

OR:

SELECT (SubselectWithOneRow) IN (subselect2)   -- but here subselect2 may
return many rows, but column counts are equal

I tried these in 8.2 without success.

Thanks,
Otto

[SQL] Bitemporal - problem with correalated subquery?

[Keith Carr]

Hi there, This is my first time posting in here and I'm hoping somebody can 
point out where I am going wrong?

I am currently trying to use Bitemporal tables. By this I mean a table with a 
valid times and transaction times. These topics are covered by Joe Celko and 
Richard Snodgrass in their respective books.

I have developed a simple schema to test the relevant constraints which are 
required to keep all the valid times and transaction times in order and to make 
sure they don't overlap. This is shown below and is done using a similar schema 
of tables for Customers, Properties and Prop_Owners as Richard Snodgrass does 
in his book.

Of course these constrains are not possible in Postgres, so I have made them as 
functions and then created triggers for them.

Everything seems to be working except for my function/trigger that maintains 
the referential integrity between the Prop_Owner and Customers tables when 
there is a "gap" in the Customers valid time or transaction time.

I am using Postgres 8.1 on Suse10.2

vt = valid time
tt = transaction time

Here is the schema:

CREATE TABLE Customers
(customer_no INTEGER NOT NULL,
 customer_name CHAR(30) NOT NULL,
 vt_begin DATE DEFAULT CURRENT_DATE,
 vt_end DATE DEFAULT DATE '-12-31',
 tt_start DATE DEFAULT CURRENT_DATE,
 tt_stop DATE DEFAULT DATE '-12-31',
   CONSTRAINT Cust_VTdates_correct
 CHECK (vt_begin <= vt_end),
   CONSTRAINT Cust_ttdates_correct
 CHECK (tt_start <= tt_stop),
   PRIMARY KEY (customer_no, vt_begin, vt_end, tt_start, tt_stop)
);


CREATE TABLE Properties
(
 prop_no INTEGER NOT NULL PRIMARY KEY,
 prop_name CHAR(20) NOT NULL
);


CREATE TABLE Prop_Owner
(
 prop_no INTEGER NOT NULL
   REFERENCES Properties (prop_no),
 customer_no INTEGER NOT NULL,
 vt_begin DATE DEFAULT CURRENT_DATE,
 vt_end DATE DEFAULT DATE '-12-31',
 tt_start DATE DEFAULT CURRENT_DATE,
 tt_stop DATE DEFAULT DATE '-12-31',
   CONSTRAINT PropOwner_VTdates_correct
 CHECK (vt_begin <= vt_end),
   CONSTRAINT PropOwner_ttdates_correct
 CHECK (tt_start <= tt_stop),
   PRIMARY KEY (prop_no, customer_no, vt_begin, vt_end, tt_start, tt_stop)
);


Here is the function/trigger I seem to be having trouble with (although there 
are others which maintain the integrity of the data - meaning records cannot 
overlap):

CREATE OR REPLACE FUNCTION P_O_integrity() RETURNS TRIGGER AS
$$
DECLARE vald INTEGER;
BEGIN
 SELECT 1 INTO vald
 WHERE NOT EXISTS
  (SELECT *
   FROM Prop_Owner AS A
-- there was a row valid in  when A started
   WHERE NOT EXISTS
(SELECT *
 FROM Customers AS B
 WHERE A.customer_no = B.customer_no
   AND B.vt_begin <= A.vt_begin AND A.vt_begin < B.vt_end
   AND B.tt_start <= A.tt_start AND A.tt_start < B.tt_stop)
-- there was a row valid in  when A ended
   OR NOT EXISTS
(SELECT *
 FROM Customers AS B
 WHERE A.customer_no = B.customer_no
   AND B.vt_begin < A.vt_end AND A.vt_end <= B.vt_end
   AND B.tt_start < A.tt_stop AND A.tt_stop <= B.tt_stop)
-- there are no gaps in  during A's period of validity
   OR EXISTS
(SELECT *
 FROM Customers AS B
 WHERE A.customer_no = B.customer_no
   AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
   AND NOT EXISTS
   (SELECT *
FROM Customers AS B2
WHERE B2.customer_no = B.customer_no
  AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
  OR (B2.tt_start <= B.tt_stop AND B.tt_stop < 
B2.tt_stop
  );
 IF NOT FOUND THEN
   RAISE EXCEPTION 'Referential integrity breached. No covering Foreign 
Key';
 END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER P_O_integrity
AFTER INSERT OR UPDATE OR DELETE  ON Prop_Owner
FOR EACH ROW EXECUTE PROCEDURE P_O_integrity();



It is this trigger/function (P_O_integrity) that does not work properly. 
Specifically it is the following part:

-- there are no gaps in  during A's period of validity
   OR EXISTS
(SELECT *
 FROM Customers AS B
 WHERE A.customer_no = B.customer_no
   AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
   AND NOT EXISTS
   (SELECT *
FROM Customers AS B2
WHERE B2.customer_no = B.customer_no
  AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end)
  OR (B2.tt_start <= B.tt_stop AND B.tt_stop < 
B2.tt_stop


This can be rewritten as follows:

SELECT customer_no
FROM Prop_Owner AS A
WHERE EXISTS
  (SELECT customer_no
   FROM Customers AS B
   WHERE A.customer_no = B.customer_no
 AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end)
  OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop))
 AND NOT EXISTS
   (SELECT custom