/* Script designed to demonstrate select count(*) problem; */

CREATE TABLE entry(
	entry_id INT4 PRIMARY KEY,
	entry_description_text TEXT,
	entry_live bool NOT NULL DEFAULT 't'
);
GRANT ALL on entry TO PUBLIC;
INSERT INTO entry VALUES (1,'entry one','t');
INSERT INTO entry VALUES (2,'entry two','t');
INSERT INTO entry VALUES (3,'entry three','t');

CREATE TABLE detail (
	detail_id INT4 PRIMARY KEY,
	entry_id INT4 NOT NULL REFERENCES entry ON DELETE NO ACTION,
	amount INT4 NOT NULL
);
GRANT ALL ON detail to PUBLIC;
CREATE INDEX detail_entry_id_idx ON detail (entry_id);
INSERT INTO detail VALUES (1,1,1);
INSERT INTO detail VALUES (2,1,5);
INSERT INTO detail VALUES (3,2,2);
INSERT INTO detail VALUES (4,2,100);
INSERT INTO detail VALUES (5,3,7);

CREATE TABLE entry_desc_fti (string varchar(25), id INT4);
GRANT ALL ON entry_desc_fti TO PUBLIC;
INSERT INTO entry_desc_fti VALUES ('one',1);



CREATE VIEW entry_view AS
SELECT entry.entry_id as id,
       entry.entry_description_text as description_text,
       min (detail.amount) as minamount
FROM   entry,
       detail
WHERE  entry_live='t'
  AND  detail.entry_id=entry.entry_id
GROUP BY 
      entry.entry_id,
      entry.entry_description_text
;
GRANT SELECT ON entry_view TO PUBLIC;


SELECT count(entry_desc_fti.id) as rating, 
       entry_view.* 
FROM entry_view, 
     entry_desc_fti 
WHERE entry_desc_fti.id=entry_view.id 
      and entry_desc_fti.string ~'^one' 
GROUP BY entry_view.id, 
         entry_view.description_text, 
         entry_view.minamount;

