Re: Variable LIMIT and OFFSET in SELECTs

Il Friday 16 November 2007 08:33:14 Tom Lane ha scritto:
> Reg Me Please <regmeplease(at)gmail(dot)com> 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