Search Postgresql Archives

Re: Variable LIMIT and OFFSET in SELECTs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Il Friday 16 November 2007 08:33:14 Tom Lane ha scritto:
> Reg Me Please <regmeplease@xxxxxxxxx> writes:
> >> The OP's complaint is that we don't allow a variable of the query's own
> >> level, but AFAICT he's still not grasped the point that that leads to an
> >> indeterminate limit value ...
> >
> > So it works, but it's not serious enough to be unlocked.
>
> You really don't have a clue what this is about, do you?
> Let me try to explain one more time.  You propose allowing
>
> 	select ... from
> 	  table1 join table2 on table1.x = table2.y
> 	  limit table1.z
>
> Now this would be somewhat well-defined if there were only one row in
> table1, or if there were many rows but they all had the same value
> in column z.  But what if that's not the case?  If there are multiple
> possible values for z then the query is just not sensible.
>
> 			regards, tom lane

You're right, maybe I have no clue at all.
(Now it seems I'm maybe a little bit less clueless).
And you are right, the fragment you propose depicts my goal.
And, once again, you are right with "this would be somewhat well-defined if
there were only one row in table1".

I know this:
---------------------------------
tmp=# CREATE SEQUENCE s1;
CREATE SEQUENCE

tmp=# CREATE SEQUENCE s2;
CREATE SEQUENCE

tmp=# CREATE TABLE seqs ( seq text primary key );
CREATE TABLE

tmp=# INSERT INTO seqs VALUES ( 's1' );
INSERT 0 1

tmp=# SELECT * from nextval( (select seq from seqs) );
 nextval
---------
       1
(1 row)

tmp=# INSERT INTO seqs VALUES ( 's2' );
INSERT 0 1

tmp=# SELECT * from nextval( (select seq from seqs) );
ERROR:  more than one row returned by a subquery used as an expression
---------------------------------

(Semantics: I put a sequence name into a table and the nextval() will be
computed over that table row content at the call time. If I put more than one
row I get an error (maybe from the planner) so I need to ensure that the table
will contain just one row).

I would not to lock features (or capabilities) to avoid people making
mistakes! Because you could hinder careful people from exploiting them.

In any case, I need to thank you all very much for the new things I've learnt
about PostgreSQL.

-- 
Reg me Please

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux