Hello,

I realised what I wrote in my last message was really confused.
You are absolutely right, I try to describe the whole situation.

1) There is a table which stores the goods of the vendors:
=#CREATE TABLE device (device_id serial, vendor_id integer, ... and some other 
columns);

2) there is another table  (named stat)  to store search hits on each vendor in daily 
distribution  which equal the sum of search hits of their devices.
Therefore i created this table as follows:
=# CREATE TABLE stat (vendor_id integer, c_date date, c_num integer);
The column c_num is for storing the total count of hits a day.

3) I created a rule to avoid the next process: "if there is already a row with the 
same vendor_id and c_date, make an UPDATE (to add the new hit count (NEW.c_num) to the 
saved one), but if it is missing the original INSERT will be executed."
=# 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;

4) and now, the query:
=> INSERT INTO stat (vendor_id, c_date, c_num) 
    SELECT vendor_id, current_date, count(*) FROM device WHERE [expressions select 
rows] GROUP BY vendor_id;
and the result (if there is a row that should be rather updated):
ERROR: ExecEvalAggref: no aggregates in this expression context

If you execute the previous query twice against an originally empty table stat,  you 
get this error for the second attempt.

This behaviour may be provided by triggers, too, but the reason why I'm forced to use 
the rule is:
 (Progammer's Guide / chapter 8. Rules versus Triggers)
-"A trigger is fired for any row affected once. A rule manipulates the parsetree or 
generates an additional one. So if many rows are affected in one statement, a rule 
issuing one extra query would usually do a better job than a trigger that is called 
for any single row and must execute his operations this many times."

Is this problem in v7.1 or I must upgrade? (I'm using v7.0.2)
Thanks in  advance,

- Papp Gyozo - 

NOTICE:  QUERY PLAN:

Merge Join  (cost=2389.66..2414.66 rows=10000 width=18)
  ->  Sort  (cost=2319.83..2319.83 rows=1000 width=4)
        ->  Seq Scan on device  (cost=0.00..2270.00 rows=1000 width=4)
              SubPlan
                ->  Seq Scan on stat  (cost=0.00..22.50 rows=10 width=8)
  ->  Sort  (cost=69.83..69.83 rows=1000 width=14)
        ->  Seq Scan on stat  (cost=0.00..20.00 rows=1000 width=14)

NOTICE:  QUERY PLAN:

Aggregate  (cost=2270.01..2270.02 rows=0 width=4)
  ->  Group  (cost=2270.01..2270.01 rows=1 width=4)
        ->  Sort  (cost=2270.01..2270.01 rows=1 width=4)
              ->  Seq Scan on device  (cost=0.00..2270.00 rows=1 width=4)
                    SubPlan
                      ->  Seq Scan on stat  (cost=0.00..22.50 rows=10 width=8)

EXPLAIN

-- verbose
NOTICE:  QUERY DUMP:

{ NESTLOOP :startup_cost 0.00 :total_cost 1.34 :rows 1 :width 18 :state <> 
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 
:resname id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 65000 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 
:varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 
:resname c_num :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR 
:typeOid 23  :opType op :oper { OPER :opno 551 :opid 177 :opresulttype 23 } :args ({ 
VAR :varno 65000 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 
:varoattno 2} { AGGREG :aggname count :basetype 0 :aggtype 23 :target { CONST 
:consttype 23 :constlen 4 :constisnull false :constvalue  4 [ 1 0 0 0 ]  :constbyval 
true } :usenulls false :aggstar true :aggdistinct false })}} { TARGETENTRY :resdom { 
RESDOM :resno 3 :restype 27 :restypmod -1 :resname ctid :reskey 0 :reskeyop 0 
:ressortgroupref 0 :resjunk true } :expr { VAR :varno 65000 :varattno 3 :vartype 27 
:vartypmod -1  :varlevelsup 0 :varnoold 5 :varoattno -1}}) :qpqual ({ EXPR :typeOid 16 
 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 
65000 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 :varoattno 1} 
{ VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1})}) :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 1.15 :rows 15 
:width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1}}) :qpqual ({ EXPR :typeOid 16  :opType subp :oper { SUBPLAN :plan { 
SEQSCAN :startup_cost 0.00 :total_cost 0.00 :rows 1 :width 8 :state <> :qptargetlist 
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY 
:resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname c_num :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 23 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2}}) :qpqual ({ EXPR :typeOid 16  
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { 
PARAM :paramkind 15 :paramid 0 :paramname <> :paramtype 23 :param_tlist <>})}) 
:lefttree <> :righttree <> :extprm ( 0) :locprm () :initplan <> :nprm 0  :scanrelid 1 
} :planid 0 :rtable ({ RTE :relname stat :ref { ATTR :relname stat :attrs <>} :relid 
259545 :inh false :inFromCl true :inJoinSet true :skipAcl false}) :setprm () :parprm ( 
0) :slink { SUBLINK :subLinkType 0 :useor false :lefthand <> :oper <> :subselect <>}} 
:args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 
1 :varoattno 1})}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 
0  :scanrelid 1 } :righttree { SEQSCAN :startup_cost 0.00 :total_cost 0.00 :rows 1 
:width 14 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 5 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 
:varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 5 :varattno 2 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 5 :varoattno 
2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 27 :restypmod -1 :resname <> 
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 5 
:varattno -1 :vartype 27 :vartypmod -1  :varlevelsup 0 :varnoold 5 :varoattno -1}}) 
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0  
:scanrelid 5 } :extprm () :locprm () :initplan <> :nprm 1 }
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..1.34 rows=1 width=18)
  ->  Seq Scan on device  (cost=0.00..1.15 rows=15 width=4)
        SubPlan
          ->  Seq Scan on stat (cost=0.00..0.00 rows=1 width=8)
  ->  Seq Scan on stat  (cost=0.00..0.00 rows=1 width=14)
NOTICE:  QUERY DUMP:

{ AGG :startup_cost 1.16 :total_cost 1.16 :rows 0 :width 4 :state <> :qptargetlist ({ 
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0 
:reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 0 :varattno 1 
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY 
:resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname c_num :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname count :basetype 0 
:aggtype 23 :target { CONST :consttype 23 :constlen 4 :constisnull false :constvalue  
4 [ 1 0 0 0 ]  :constbyval true } :usenulls false :aggstar true :aggdistinct false }}) 
:qpqual <> :lefttree { GRP :startup_cost 1.16 :total_cost 1.16 :rows 1 :width 4 :state 
<> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR 
:varno 0 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 
1}}) :qpqual <> :lefttree { SORT :startup_cost 1.16 :total_cost 1.16 :rows 1 :width 4 
:state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 
:restypmod -1 :resname <> :reskey 1 :reskeyop 66 :ressortgroupref 0 :resjunk false } 
:expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 
:varoattno 1}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 1.15 
:rows 1 :width 4 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 
:restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  
:varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual ({ EXPR :typeOid 16  :opType not 
:oper <> :args ({ EXPR :typeOid 16  :opType subp :oper { SUBPLAN :plan { SEQSCAN 
:startup_cost 0.00 :total_cost 0.00 :rows 1 :width 8 :state <> :qptargetlist ({ 
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname id :reskey 0 
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY 
:resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname c_num :reskey 0 :reskeyop 
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 23 
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2}}) :qpqual ({ EXPR :typeOid 16  
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { 
PARAM :paramkind 15 :paramid 0 :paramname <> :paramtype 23 :param_tlist <>})}) 
:lefttree <> :righttree <> :extprm ( 0) :locprm () :initplan <> :nprm 0  :scanrelid 1 
} :planid 0 :rtable ({ RTE :relname stat :ref { ATTR :relname stat :attrs <>} :relid 
259545 :inh false :inFromCl true :inJoinSet true :skipAcl false}) :setprm () :parprm ( 
0) :slink { SUBLINK :subLinkType 0 :useor false :lefthand <> :oper <> :subselect <>}} 
:args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 
1 :varoattno 1})})}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> 
:nprm 0  :scanrelid 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0  
:nonameid 0 :keycount 1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0  
:numCols 1 :tuplePerGroup true } :righttree <> :extprm () :locprm () :initplan <> 
:nprm 1 }
NOTICE:  QUERY PLAN:

Aggregate  (cost=1.16..1.16 rows=0 width=4)
  ->  Group  (cost=1.16..1.16 rows=1 width=4)
        ->  Sort  (cost=1.16..1.16 rows=1 width=4)
              ->  Seq Scan on device  (cost=0.00..1.15 rows=1 width=4)
                    SubPlan
                      ->  Seq Scan on stat  (cost=0.00..0.00 rows=1 width=8)

EXPLAIN



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Reply via email to