[SQL] Problem with joins

[Jean-Marc Libs]

Hi all,

I am trying to do something unusual (for me) and I can't quite
find the relevant part in the documentation (no mention to joins
in the insert section, or the index).

I want to get a list of data sources with the corresponding data,
if the data exists, or with null, if the data doesn't. But anyway
I need the data sources (all of them).

So I have tried:
select source_name,data_value from source,data where data_source_id=source_id

but it lacks those rows where there is no data in "data" table.

I have also tried:
select source_name,data_value from source,data where data_source_id=source_id union 
select source_name,source_id,NULL from source,data

This is a bit better, in the sense that I get back all I need, but there
are too many lines: when there is data, I get the line with the data value
and also with NULL.

I strongly suspect that there is a more elegant way of doing it:
a solution or pointers to relevant online litterature would be welcome.

Thanks,
Jean-Marc Libs

-- 
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net
Professionnel : [EMAIL PROTECTED] 
Lieu de travail : [EMAIL PROTECTED]

Antw: [SQL] Problem with joins

[Gerhard Dieringer]

Jean-Marc Libs wrote:
>...
>I have also tried:
>select source_name,data_value from source,data where data_source_id=source_id union 
>select source_name,source_id,NULL from source,data

>This is a bit better, in the sense that I get back all I need, but there
>are too many lines: when there is data, I get the line with the data value
>and also with NULL.
>...

You are on the right way. Change your querry to 

select source_name,data_value 
from source,data 
where data_source_id=source_id 
union 
select source_name,source_id
from source
WHERE source_id NOT IN (SELECT source_id FROM data);

and you will get your expected result.

BTW this simulates an outer join.

Gerhard

[SQL] GROUP by

[Antti Linno]

I have table job, and I want to select data from it, grouping by
workgroups. When I use 
 select id,name from job where workgroup='top leaders' AND ...  group by
workgroup;
When I want to group, I get 
ERROR:  Illegal use of aggregates or non-group column in target list
I'm confused, why does query work only with sum,min etc. alone in
select.

Antti

Re: [SQL] GROUP by

[Jerome Alet]

On Wed, 5 Jul 2000, Antti Linno wrote:

> I have table job, and I want to select data from it, grouping by
> workgroups. When I use 
>  select id,name from job where workgroup='top leaders' AND ...  group by
> workgroup;

this query is probably incorrect: because of the "where workgroup=" the
"group by workgroup" can't do anything.

bye,

Jerome ALET - [EMAIL PROTECTED] - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE

[SQL] Group BY ...(cont.)

[Antti Linno]

Ok, if I want to use group by, then I have to put every select field
afterwards to group by. But I do want to group by one field.
Hence
select workgroup,id from job group by workgroup,id;
would create groups of 1?
But I want to group by workgroups.
Sorry if my explanation is fuzzy.
A.

Antw: [SQL] GROUP by

[Gerhard Dieringer]

Antti Linno <[EMAIL PROTECTED]> wrote:

> I have table job, and I want to select data from it, grouping by
> workgroups. When I use 
>  select id,name from job where workgroup='top leaders' AND ...  group by
> workgroup;
> When I want to group, I get 
> ERROR:  Illegal use of aggregates or non-group column in target list
> I'm confused, why does query work only with sum,min etc. alone in
> select.
> 
> Antti

You can use GROUP BY only in conjunction with aggregat functions. Then you have to 
gruop by all attributes in the target list, that are not aggregated.

Example:
select id,name, workgroup, count(*) 
from job 
where workgroup='top leaders' AND ...  
group by id,name,workgroup;

-
Gerhard

Re: [SQL] GROUP by

[Jesus Aneiros]

You can use group by without having an aggregate operator but if you use
an aggregate you have to put the group by column int the select.

By the way what is after the AND? I hope it is not another workgroup
condition because it doesn't have much sense, I think. If you are
filtering different types of workgroup you should use OR.

Jesus.

On Wed, 5 Jul 2000, Antti Linno wrote:

> I have table job, and I want to select data from it, grouping by
> workgroups. When I use 
>  select id,name from job where workgroup='top leaders' AND ...  group by
> workgroup;
> When I want to group, I get 
> ERROR:  Illegal use of aggregates or non-group column in target list
> I'm confused, why does query work only with sum,min etc. alone in
> select.
> 
> Antti
> 
> 

Re: [SQL] Group BY ...(cont.)

[[EMAIL PROTECTED]]

E.g.

select workgroup from job group by workgroup;


will that do?


Troy

> 
> Ok, if I want to use group by, then I have to put every select field
> afterwards to group by. But I do want to group by one field.
> Hence
> select workgroup,id from job group by workgroup,id;
> would create groups of 1?
> But I want to group by workgroups.
> Sorry if my explanation is fuzzy.
> A.
> 
> 
> 

[SQL] PLPGSQL - does not support more than 8 args?

[Grigori Soloviov]

 Hi. I just came accross the big trouble.
 " ERROR:  Procedures cannot take more than 8 arguments"
 Really?!? Does anybody know what to do to make it take 9 and more argments.
 grishick
 [EMAIL PROTECTED]

Re: [SQL] Group BY ...(cont.)

[Jesus Aneiros]

No, you don't you could say:

SELECT workgroup, id
FROM job
GROUP BY workgroups;

On Wed, 5 Jul 2000, Antti Linno wrote:

> Ok, if I want to use group by, then I have to put every select field
> afterwards to group by. But I do want to group by one field.
> Hence
> select workgroup,id from job group by workgroup,id;
> would create groups of 1?
> But I want to group by workgroups.
> Sorry if my explanation is fuzzy.
> A.
> 
> 

[SQL] GROUP by finish&&last day of month

[Antti Linno]

Ok, if I want to get non-aggregat data in groups, I use order by. This 
group by seemed so logical though, but the fruit that u can't have, is
usually the most sweet.

New question, how to get the last day of month(order data by last day of
month). And to prevent chain letter from misunderstanding, no I don't
intend to look it up from calendar and then input it manually. 

A.

Re: [SQL] GROUP by finish&&last day of month

[Jerome Alet]

On Wed, 5 Jul 2000, Antti Linno wrote:

> New question, how to get the last day of month(order data by last day of
> month). 

I'm sorry but I don't understand how you could order something by a value
(last day of month).

you can only order things by a field (e.g. a date field) or fields, in
increasing or decreasing order. 

e.g.:

you can do:

SELECT id,eventdate FROM mytable ORDER BY eventdate DESC;

and not:

SELECT id,eventdate FROM mytable ORDER BY '2000-01-31' DESC; 

the latter one doesn't mean anything to me, nor to SQL I suppose.

bye,

Jerome ALET - [EMAIL PROTECTED] - http://cortex.unice.fr/~jerome
Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 
28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE

Re: [SQL] Which procedural language to use for functions.

[Jan Wieck]

Saltsgaver, Scott wrote:
> Which procedural language is best for writing PostgreSQL functions:
> 'PL/pgSQL', 'PL/perl', or 'PL/Tcl'?
>
> I know that I can't do table creates in 'PL/pgSQL'?
>
> Is there an obvious choice as far speed and size?

PL/Tcl has the advantage that it can do the table creates you
want.  OTOH if your frontend app is written in Perl, it's not
that   likely   that   one  of  the  app  programmers  easily
understands a function.

I have no benchmarking  information  between  the  procedural
languages.   If  that's really an issue, write your functions
and triggers in C.


Jan

--

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

[SQL] Aww, sorry (last day of month)

[Antti Linno]

I thought, I finished that group by theme. 

What i ment was, that I have information about workers, and I have to make
report on them. Report should contain status about workers with the
restriction , that the data is about last day of the month. I use perl as
a scripting language, and how the hell should I know how many days were in
that particular month (28,29,30,31) :). If there are no means by doing it
in sql, I write a code to ask for a , mmm, special day. *sigh*
Maybe something with date_trunc?

Thanx for discussion though.

Antti,
greetings from estionia :P

Re: [SQL] GROUP by finish&&last day of month

[[EMAIL PROTECTED]]

Not quite sure what you mean, but how about this:

select date_part('day', 'Jul 01 00:00:00 2000 PDT'::datetime-1);

You must specify Jul for June, i.e. always the following month.
You could get around that too, but since I don't know why you
need to do what you asked, I'll leave it at this.



Troy


> 
> Ok, if I want to get non-aggregat data in groups, I use order by. This 
> group by seemed so logical though, but the fruit that u can't have, is
> usually the most sweet.
> 
> New question, how to get the last day of month(order data by last day of
> month). And to prevent chain letter from misunderstanding, no I don't
> intend to look it up from calendar and then input it manually. 
> 
> A.
> 
> 
> 

Re: [SQL] GROUP by finish&&last day of month

[Patrick Jacquot]

Antti Linno wrote:

> Ok, if I want to get non-aggregat data in groups, I use order by. This
> group by seemed so logical though, but the fruit that u can't have, is
> usually the most sweet.
>
> New question, how to get the last day of month(order data by last day of
> month). And to prevent chain letter from misunderstanding, no I don't
> intend to look it up from calendar and then input it manually.
>
> A.

to get last day of month :
1) use date-trunc to truncate to first day of month
2) add one month
3) substract 1 day

[SQL] Median

[Kermani, Bahram]

Hello,
 
I 
am trying to do a Median or Trimmed-mean operation in postgreSQL. I was 
wondering if anybody knew how to do it. I appreciate it if you reply to my email 
address.
 
Thanks,
Bahram 
Kermani
[EMAIL PROTECTED]

Re: Antw: [SQL] Problem with joins

[Jean-Marc Libs]

On Wed, 5 Jul 2000, Gerhard Dieringer wrote:

> Jean-Marc Libs wrote:
> >...
> >I have also tried:
> >select source_name,data_value from source,data where data_source_id=source_id union 
>select source_name,source_id,NULL from source,data
> 
> >This is a bit better, in the sense that I get back all I need, but there
> >are too many lines: when there is data, I get the line with the data value
> >and also with NULL.
> >...
> 
> You are on the right way. Change your querry to 
> 
> select source_name,data_value 
> from source,data 
> where data_source_id=source_id 
> union 
> select source_name,source_id
> from source
> WHERE source_id NOT IN (SELECT source_id FROM data);
> 
> and you will get your expected result.
> 
> BTW this simulates an outer join.

Oh, I wasn't aware that outer joins aren't supported :-(

Well, I found that it does kinda work. My app is actually more
complicated than this, so now I have:

select source_name,data_value 
from source,data
where data_source_id=source_id and "conditions on source"
union
select source_name,NULL
from source
WHERE source_id NOT IN (SELECT data_source_id FROM data where "my conditions on data")

I get exactly the rows I wanted, and I am very glad for the tip (that
should go in a FAQ, it it isn't already).

But now I wonder where I could put my 'order by source_order' statement,
as I can't figure out how to put the rows in the proper order :-(

Thanks for the prompt answer anyway,
Jean-Marc Libs

-- 
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net
Professionnel : [EMAIL PROTECTED] 
Lieu de travail : [EMAIL PROTECTED]

[SQL] ERROR: ExecEvalAggref: no aggregates in this expression context

[Paul McGarry]

Hello,

Can someone tell me what this error message means:
==
ERROR: ExecEvalAggref: no aggregates in this expression context
==

Does it mean that there aren't any aggregate in the expression
context when there should be. Does it mean there are and there
shouldn't be?

For that matter, what are aggregates and in what expression
context should they be or not be in?

Could it be because I am trying to group around something like
entry_view.minprice,
entry_view.maxprice
where minprice and maxprice are defined in as
min (item.item_price) as minprice,
max (item.item_price) as maxprice
when the entry_view view is defined?

Thanks

-- 
Paul McGarrymailto:[EMAIL PROTECTED] 
Systems Integrator  http://www.opentec.com.au 
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9878 1744 
North Ryde NSW 2113 Fax:   (02) 9878 1755

Re: [SQL] Problem with joins

[Thomas Lockhart]

> I want to get a list of data sources with the corresponding data,
> if the data exists, or with null, if the data doesn't. But anyway
> I need the data sources (all of them).

You want an outer join. Postgres doesn't have that yet, but you can
mimic it.

> I have also tried:
>  select source_name,data_value
>   from source,data where data_source_id=source_id
>  union
>  select source_name,source_id,NULL from source,data
> This is a bit better, in the sense that I get back all I need, but there
> are too many lines: when there is data, I get the line with the data value
> and also with NULL.

Close. Try

  select source_name,data_value
   from source,data where data_source_id=source_id
  union
  select source_name,source_id,NULL from source
   where source_id not in (select data_source_id from data);

 - Thomas

[SQL] bug in using index scans?

[Martin, Sylvain R. (LNG)]

I've notice on certain queries, I was waiting a long time for a return so I
set out to troubleshoot something here's what I ran into...
When I do a explain on a select looking for 'peripherals & access' it uses
sequential scan but any other keyword uses index scan.
I've ran the vacuum analyze on PI_Keywords but comes up with the same
results. Can anyone offer some insight or confirm this as a bug?

rvbs=# \d PI_Keywords
   Table "pi_keywords"
 Attribute |   Type   | Modifier 
---+--+--
 keyword   | char(50) | 
 productid | integer  | 
Index: pi_keywords_idx

rvbs=# select distinct (keyword) from PI_Keywords where keyword like '%&%'
limit 10;
   keyword   

 adult training & ed   
 atlas & mapping   
 books & manuals   
 chips & modules   
 education & training  
 peripherals & access  
(6 rows)

rvbs=# explain select * from PI_Keywords where keyword like 'peripherals &
access'; 
NOTICE:  QUERY PLAN:

Seq Scan on pi_keywords  (cost=0.00..6596.18 rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'chips &
modules'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'education &
training'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'adult training
& ed'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'peripherals
&%'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..4.40 rows=12632
width=16)

EXPLAIN

rvbs=# select distinct(keyword) from PI_Keywords where keyword like
'peripherals &%';
  keyword   

 peripherals & access  
(1 row)

Sylvain Martin
USA-Response Team
(937) 865-6800 x4432
Pager: (937) 636-1171

Re: [SQL] Aww, sorry (last day of month)

[Thomas Lockhart]

> What i ment was, that I have information about workers, and I have to make
> report on them. Report should contain status about workers with the
> restriction , that the data is about last day of the month. I use perl as
> a scripting language, and how the hell should I know how many days were in
> that particular month (28,29,30,31) :). If there are no means by doing it
> in sql, I write a code to ask for a , mmm, special day. *sigh*
> Maybe something with date_trunc?

Someone else had the suggestion, but specifically something like

  ... where d1 >= (date_trunc('month', timestamp 'today') - interval '1
day')
   and d1 < date_trunc('month', timestamp 'today');

should get you data for the previous month. Substitute the "today" field
for something else as you may require.

- Thomas

RE: [SQL] bug in using index scans? More Investigating

[Martin, Sylvain R. (LNG)]

just for the heck of it I decided to run the following in case it helped

rvbs=# explain select count(*) from PI_Keywords where keyword like
'peripherals & access%';
NOTICE:  QUERY PLAN:

Aggregate  (cost=35.98..35.98 rows=1 width=4)
  ->  Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..4.40
rows=12632 width=4)

EXPLAIN
rvbs=# explain select count(*) from PI_Keywords where keyword like
'peripherals & access';
NOTICE:  QUERY PLAN:

Aggregate  (cost=6599.33..6599.33 rows=1 width=4)
  ->  Seq Scan on pi_keywords  (cost=0.00..6596.18 rows=1263 width=4)

EXPLAIN
Apparently adding a % at the end made it use the index scan.

-Original Message-
From: Martin, Sylvain R. (LNG) [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 05, 2000 10:56 AM
To: '[EMAIL PROTECTED]'
Subject: [SQL] bug in using index scans?


I've notice on certain queries, I was waiting a long time for a return so I
set out to troubleshoot something here's what I ran into...
When I do a explain on a select looking for 'peripherals & access' it uses
sequential scan but any other keyword uses index scan.
I've ran the vacuum analyze on PI_Keywords but comes up with the same
results. Can anyone offer some insight or confirm this as a bug?

rvbs=# \d PI_Keywords
   Table "pi_keywords"
 Attribute |   Type   | Modifier 
---+--+--
 keyword   | char(50) | 
 productid | integer  | 
Index: pi_keywords_idx

rvbs=# select distinct (keyword) from PI_Keywords where keyword like '%&%'
limit 10;
   keyword   

 adult training & ed   
 atlas & mapping   
 books & manuals   
 chips & modules   
 education & training  
 peripherals & access  
(6 rows)

rvbs=# explain select * from PI_Keywords where keyword like 'peripherals &
access'; 
NOTICE:  QUERY PLAN:

Seq Scan on pi_keywords  (cost=0.00..6596.18 rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'chips &
modules'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'education &
training'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'adult training
& ed'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..3652.83
rows=1263 width=16)

EXPLAIN
rvbs=# explain select * from PI_Keywords where keyword like 'peripherals
&%'; 
NOTICE:  QUERY PLAN:

Index Scan using pi_keywords_idx on pi_keywords  (cost=0.00..4.40 rows=12632
width=16)

EXPLAIN

rvbs=# select distinct(keyword) from PI_Keywords where keyword like
'peripherals &%';
  keyword   

 peripherals & access  
(1 row)

Sylvain Martin
USA-Response Team
(937) 865-6800 x4432
Pager: (937) 636-1171

Re: [SQL] Median

[omid omoomi]

Hi,
I'll be glad if you describe more details about the problem. Is it a kind of 
statistical analysis or what?
Omid Omoomi

>From: "Kermani, Bahram" <[EMAIL PROTECTED]>
>To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
>Subject: [SQL] Median
>Date: Fri, 30 Jun 2000 17:37:06 -0700
>
>Hello,
>
>I am trying to do a Median or Trimmed-mean operation in postgreSQL. I was
>wondering if anybody knew how to do it. I appreciate it if you reply to my
>email address.
>
>Thanks,
>Bahram Kermani
>[EMAIL PROTECTED] 


Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Re: [SQL] ERROR: ExecEvalAggref: no aggregates in this expression context

[Tom Lane]

Paul McGarry <[EMAIL PROTECTED]> writes:
> Can someone tell me what this error message means:
> ERROR: ExecEvalAggref: no aggregates in this expression context

> Does it mean that there aren't any aggregate in the expression
> context when there should be.

Yup.  ExecEvalAggref is supposed to fetch the result of a (previously
computed) aggregate function.  It's unhappy because it's not finding
anything to return.  This is a bug --- can we see a complete example
that causes it?

> For that matter, what are aggregates

SUM(), COUNT(), MIN(), that sort of thing...

regards, tom lane

Re: [SQL] ERROR: ExecEvalAggref: no aggregates in thisexpressioncontext

[Paul McGarry]

Hi Tom,

Tom Lane wrote:
> > ERROR: ExecEvalAggref: no aggregates in this expression context
> > Does it mean that there aren't any aggregate in the expression
> > context when there should be.
> Yup.  ExecEvalAggref is supposed to fetch the result of a (previously
> computed) aggregate function.  It's unhappy because it's not finding
> anything to return.  This is a bug --- can we see a complete example
> that causes it?

The view that I am experiencing this error on is rather nasty, so
much so that I am going to solve the problem it was supposed to
solve in another way.

On another (possibly related) note, the same view seems to pose
problems when I do a 'SELECT count(*) from view_name'.

If I do a 'SELECT * from view_name' and get 8 rows of data, when I
 do a 'SELECT count(*) from view_name' I get 8 rows of the 
number 1 returned. 

In any case I'll try and distill both of these down to a slightly 
less horrific testcase and send it in.

-- 
Paul McGarrymailto:[EMAIL PROTECTED] 
Systems Integrator  http://www.opentec.com.au 
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9878 1744 
North Ryde NSW 2113 Fax:   (02) 9878 1755

Re: [SQL] ERROR: ExecEvalAggref: no aggregates in thisexpressioncontext

[Paul McGarry]

Paul McGarry wrote:

> If I do a 'SELECT * from view_name' and get 8 rows of data, when I
>  do a 'SELECT count(*) from view_name' I get 8 rows of the
> number 1 returned.

I've attached a script which will generate a bunch of tables and
a view then do a select * and select count(*) on that view. It
is almost certainly to do with the GROUPing used in the view.

The ouput I get is:

select * from entry_view;
 id | description_text | parent_id | minamount 
+--+---+---
  1 | entry one| 0 | 1
  2 | entry two| 1 | 2
  3 | entry three  | 2 | 7
(3 rows)

select count(*) from entry_view;
 count 
---
 2
 2
 1
(3 rows)

That doesn't make much sense to me...

Postgres 7.0.2 by the way.

I'll work on the more complex case where I got the 
ERROR: ExecEvalAggref error message now.

-- 
Paul McGarrymailto:[EMAIL PROTECTED] 
Systems Integrator  http://www.opentec.com.au 
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9878 1744 
North Ryde NSW 2113 Fax:   (02) 9878 1755
 countbug.sql

[SQL] ALTER TABLE ADD COLUMN syntax question.

[Robert B. Easter]

I used the command below to alter a table and it took with no problem, but the
REFERENCES appears to have been ignored since I can put invalid numbers into
last_post_id.  Are there plans to add the ability to specify column
constrainsts with ALTER TABLE ADD COLUMN in the future?  For now, I suppose I
can use ALTER TABLE ADD CONSTRAINT to make a table constraint for last_post_id.
 I hope it will not make things too slow.  Are table constraints slower than
column constraints? 

ALTER TABLE topic ADD COLUMN last_post_id INTEGER REFERENCES post ON DELETE
SET NULL;

 -- 
Robert

Re: [SQL] ERROR: ExecEvalAggref: no aggregates in this expressioncontext

[Tom Lane]

Paul McGarry <[EMAIL PROTECTED]> writes:
>> Yup.  ExecEvalAggref is supposed to fetch the result of a (previously
>> computed) aggregate function.  It's unhappy because it's not finding
>> anything to return.  This is a bug --- can we see a complete example
>> that causes it?

> The view that I am experiencing this error on is rather nasty, so
> much so that I am going to solve the problem it was supposed to
> solve in another way.

View?  Hmm, we know that views involving GROUP BY or aggregates
don't work very well.  We hope to fix those problems in 7.2, but
right now there's probably not much that can be done about it.
I'd recommend avoiding views that use those features for now.

regards, tom lane

Re: [SQL] ERROR: ExecEvalAggref: no aggregates in thisexpressioncontext

[Paul McGarry]

Tom Lane wrote:
> View?  Hmm, we know that views involving GROUP BY or aggregates
> don't work very well.  We hope to fix those problems in 7.2, but
> right now there's probably not much that can be done about it.
> I'd recommend avoiding views that use those features for now.

Yes, I've just discovered the Todo list :) Oh well, it probably
needed rethinking anyway.I want fast retrieval over fast
update so I probably want to do my min() and max() magic with
triggers on insert/update/delete rather than on each select
with the view.

In any case I have attached some SQL which sets up tables and
generates the ExecEvalAggref I was getting in case it is of
any use to anyone.

Cheers.

-- 
Paul McGarrymailto:[EMAIL PROTECTED] 
Systems Integrator  http://www.opentec.com.au 
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park RoadPhone: (02) 9878 1744 
North Ryde NSW 2113 Fax:   (02) 9878 1755
 execeval.sql