sets and insert-select with rule - Mailing list pgsql-general

From Gyozo Papp
Subject sets and insert-select with rule
Date
Msg-id 02ea01c0c4f7$d14fad80$d448c5d5@jaguar
Whole thread Raw
Responses Re: sets and insert-select with rule
List pgsql-general
Hello,
 
 
First of all: I' m using postgresql v7.0.2 on debian 2.2. (and I have two completely different questions)
 
 
i) What is the most effective representation of type "set" in postgres?
As I remember there is no attribute type to represent definitely sets. (Please, correct me if  I'm wrong.)
Arrays  and bitstrings seem to serve this purpose, but what about performance regarding to the commonly used set operation (is in, union, etc)?
 
ii) When  I execute the following query:
 
=> INSERT INTO stat (vendor_id, c_date, c_num) SELECT vendor_id, current_date, count(*) FROM device_id in (...) GROUP BY vendor_id;
I get an error message something like "ExecAggrEval(), aggregate function is not available here".
(this is surely not what I get back if it's needed I post the right message )
 
I know what cause the problem...
I create a rule on table stat that turns an INSERT query into UPDATE if there's already a record with the same vendor_id and c_date.
=> CREATE RULE  r_logstat AS ON INSERT TO stat
->  WHERE EXISTS (SELECT * FROM stat WHERE vendor_id = new.vendor_id AND c_date= new.c_date)
->  DO INSTEAD UPDATE stat SET c_num = c_num + new.c_num WHERE vendor_id = new.vendor_id AND c_date= new.c_date;
if the rule is not invoked everything goes fine, but if  there is a row that should be updated instead of INSERT the query fails.
How can I solve this ? Maybe an upgrade to v7.1 blows the whole thing out?

- Papp Gyozo -

pgsql-general by date:

Previous
From: "Brett W. McCoy"
Date:
Subject: Re: anti Christian bias?
Next
From: "Len Morgan"
Date:
Subject: 7.1 SRPM woes (Mandrake 7.2)