Re: [SQL] sub-select, view and sum()

2003-01-06 Thread Gary Stainburn
On Monday 06 January 2003 6:31 pm, Stephan Szabo wrote: > On Mon, 6 Jan 2003, Gary Stainburn wrote: > > create view turn_details as > > select t.*, d.sid as dsid, d.sname as dname, > > f.sid as fsid, f.sname as fname, > > (select sum(r.rmiles) as rmiles from rides r where

Re: [SQL] sub-select, view and sum()

2003-01-06 Thread Stephan Szabo
On Mon, 6 Jan 2003, Gary Stainburn wrote: > create view turn_details as > select t.*, d.sid as dsid, d.sname as dname, > f.sid as fsid, f.sname as fname, > (select sum(r.rmiles) as rmiles from rides r where r.rtid = tid) > as rmiles > from turns t >

Re: [SQL] Sorry, to many clients already

2003-01-06 Thread Robert Treat
On Mon, 2003-01-06 at 09:12, cristi wrote: > When I'm trying to connect I have this error message: > > Something unusual has occured to cause the driver to fail.Please report this > exception: java.sql.SQLException: Sorry, to many clients already. > > > What should I do? > I might suggest post

[SQL] sub-select, view and sum()

2003-01-06 Thread Gary Stainburn
Hi folks, I've got 3 tables (plus others), and want to create a view joining them. Below are the two main tables and the view I'm trying to create. Anyone, got any idea how I need to word the 'create view' create table turns (-- Turns Table. Hold details of my turns tid

Re: [SQL] order by and aggregate

2003-01-06 Thread Tomasz Myrta
Tom Lane wrote: I don't understand what you think is wrong with this solution. It works fine for me: regression=# create view vv as regression-# select max(unique1) from (select unique1 from tenk1 regression(# order by unique2) x; CREATE VIEW regression=# explain select * from vv;

Re: [SQL] order by and aggregate

2003-01-06 Thread Tomasz Myrta
Tom Lane wrote: Tomasz Myrta writes: >Standard Postgresql aggregate functions don't need sorted data, but my >function needs. Look at the data: > >3 3 >-21 >6 7 *** max_sum=7 >-34 >2 6 But if the input data is sorted into increasing order, then the largest ru

Re: [SQL] order by and aggregate

2003-01-06 Thread Tom Lane
Tomasz Myrta <[EMAIL PROTECTED]> writes: > Standard Postgresql aggregate functions don't need sorted data, but my > function needs. Look at the data: > > 3 3 > -21 > 6 7 *** max_sum=7 > -34 > 2 6 But if the input data is sorted into increasing order, then

Re: [SQL] order by and aggregate

2003-01-06 Thread Tom Lane
Tomasz Myrta <[EMAIL PROTECTED]> writes: > I found I can obey this using subselect: > select >maxsum(X.value) > from > (select value >from some_table >order by some_field) X > I can't create subselect, because I want to change this query into a > view. In my case postgresql doesn't

Re: [SQL] order by and aggregate

2003-01-06 Thread Tomasz Myrta
[EMAIL PROTECTED] wrote: Ah - so it's maximum of a running-total rather than a sum. Sorry, my english still has a lot of black-holes :-( AFAIK you are out of luck with aggregate functions. The order data is supplied to them is *not* defined - the "order by" operates just before results are ou

Re: [SQL] order by and aggregate

2003-01-06 Thread dev
> Richard Huxton wrote: > >> On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote: >> >> >Hi >> >I created my own aggregate function working as max(sum(value)) >> >It adds positive and negative values and finds maximum of this sum. >> >To work properly this function needs data to be sorted. >> >> >>

[SQL] Sorry, to many clients already

2003-01-06 Thread cristi
When I'm trying to connect I have this error message: Something unusual has occured to cause the driver to fail.Please report this exception: java.sql.SQLException: Sorry, to many clients already. What should I do? ---(end of broadcast)--- TIP 6

Re: [SQL] order by and aggregate

2003-01-06 Thread Tomasz Myrta
Richard Huxton wrote: On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote: >Hi >I created my own aggregate function working as max(sum(value)) >It adds positive and negative values and finds maximum of this sum. >To work properly this function needs data to be sorted. I'm not sure that an aggre

Re: [SQL] order by and aggregate

2003-01-06 Thread Richard Huxton
On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote: > Hi > I created my own aggregate function working as max(sum(value)) > It adds positive and negative values and finds maximum of this sum. > To work properly this function needs data to be sorted. I'm not sure that an aggregate function should r

Re: [SQL] Grant execute on functions; related objects permissions ?

2003-01-06 Thread Bruno Wolff III
On Mon, Jan 06, 2003 at 12:45:25 +0200, Tambet Matiisen <[EMAIL PROTECTED]> wrote: > btw, views "execute" also with owner's rights, ie if you grant select on view, you >do not have to grant select on every table used in view. Still current_user in view >returns "caller", while maybe it should r

[SQL] order by and aggregate

2003-01-06 Thread Tomasz Myrta
Hi I created my own aggregate function working as max(sum(value)) It adds positive and negative values and finds maximum of this sum. To work properly this function needs data to be sorted. select maxsum(value) from some_table order by some_field doesn't work: ERROR: Attribute some_table.som

Re: [SQL] Grant execute on functions; related objects permissions ?

2003-01-06 Thread Tambet Matiisen
In PostgreSQL 7.3 you have option to execute function with owner's rights or caller's rights. Default is caller's rights (as it was before 7.3), you probably want owner's rights. See development version of docs: http://developer.postgresql.org/docs/postgres/sql-createfunction.html btw, views "

[SQL] Grant execute on functions; related objects permissions ?

2003-01-06 Thread Cédric Dufour (public)
Hello, PostgreSQL 7.3 happily introduced permissions on functions. Now, having granted execution to a given function to a given user, I find myself with "access denied" errors on the objects that the function actually uses (e.g. a table on which it makes a select). So: 1. Am I missing something a