Hi,
> Here the result of "SELECT * FROM test1v" depends on who issued the query.
As a more general case, I sometimes load parameters into a utility table,
and use them to dynamically restrict the view's output. Downside: it's a
multistatement operation... however, when wrapping complex queries
Jay Levitt writes:
> * You want contextual queries.
>
> (I guess this is a special case of "you need non relational features".)
>
> In my case, I want all queries against content to be filtered by their
> relevance to the current user. That can't go into a view, because
> views don't have paramet
Alban Hertroys wrote:
select questions.id from questions
join (
select u.id from users as u
group by u.id
) as s
on s.id = questions.user_id
where questions.id = 1;
You could write that as:
select questions.id
from questions as q
where exists (select 1 from users as u where u.id = q.user_id
>>> [1] Since this is my current favorite problem, the pathological case is:
>>>
>>> select questions.id
>>> from questions
>>> join (
>>> select u.id
>>> from users as u
>>> group by u.id
>>> ) as s
>>> on s.id = questions.user_id
>>> where questions.id = 1;
>>>
>>> With users.id as a primary
Merlin Moncure wrote:
SQL has a very powerful abstraction feature: it's called a view. Good
use of views is a key design feature for complex databases.
Functions are generally not a good choice for query abstraction unless:
One more:
* You want contextual queries.
(I guess this is a special
On Tue, Dec 13, 2011 at 4:27 PM, Jay Levitt wrote:
> Merlin Moncure wrote:
>>
>> Breaking your large queries into functions OTOH can make significant
>> changes to the plan, often to the worse.
>
>
> As an end-user, I think this is an area where PostgreSQL could really stand
> out (that and the mo
Merlin Moncure wrote:
Breaking your large queries into functions OTOH can make significant
changes to the plan, often to the worse.
As an end-user, I think this is an area where PostgreSQL could really stand
out (that and the moon launch). In Rails-land, you don't have The DBA that
writes qu
On Sun, Dec 11, 2011 at 9:10 PM, Craig Ringer wrote:
> On 12/12/2011 09:15 AM, David Johnston wrote:
>>
>> Use a WITH clause on the SELECT statement.
>
> Note that WITH is an optimisation fence, so if you're relying on Pg pushing
> WHERE clauses down into subqueries or anything like that you may f
On 12/12/2011 09:15 AM, David Johnston wrote:
Use a WITH clause on the SELECT statement.
Note that WITH is an optimisation fence, so if you're relying on Pg
pushing WHERE clauses down into subqueries or anything like that you may
find that your query runs a LOT slower when broken up as WITH exp
Inlined.
David J.
On Dec 11, 2011, at 19:46, Robert James wrote:
> I have a very long query. Due to the planner and good indexing, it
> runs quite fast. But it's so long, it's quite hard to follow.
>
> I'm trying to break it up into pieces, but am running up against
> limits of SQL. Can you
I have a very long query. Due to the planner and good indexing, it
runs quite fast. But it's so long, it's quite hard to follow.
I'm trying to break it up into pieces, but am running up against
limits of SQL. Can you help me with any of these problems?
1.
SELECT
AS A,
AS C,
AS D
...
I'd li
11 matches
Mail list logo
