Search code examples
sqlpostgresqlviewtriggers

In PostgreSQL is there a way to impose a LIMIT to queries automatically?

Initial Question

In PostgreSQL is there a way to impose a LIMIT clause to queries automatically?

Suppose I have a table restaurant:

create table restaurant (
  id uuid primary key,
  name text,
  created_at timestamp)

First, I tried a view:

create view restaurant_limited 
as 
select * 
from restaurant 
order by created_at 
limit 3;

The problem with that is you can't apply a predicate and have it work, because the predicate gets applied after the LIMIT.

Next, I considered a rewrite rule. However, as far as I can tell, the rule system allows you to substitute a new query, but it doesn't give you much power to rewrite an existing query.

Next, I considered a trigger, but triggers only seem to apply to DML, not to queries.

I suppose a function or a procedure could do it, but now we're getting out of the realm of a pure SQL interface. Surely there must be some way to do this. Any ideas?

Addendum

The reason I didn't want to apply the predicates first and then apply the LIMIT is because I wanted it to be general and support a wide variety of query patterns. If users can submit more-or-less any query, then I don't know the predicates ahead of time. Essentially, what I wanted was something whose logical design, if not physical design, would be something like this.

  1. Accept an arbitrary SELECT query against a given table.
  2. Treat that query as a sub-query of an outer query.
  3. Automatically add a LIMIT clause in that outer query.

I could absolutely do this at the SQL console. I could also do it in a middle layer, if the SQL is being generated within a middle layer (e.g., Python or Java). What I was hoping was that there was some way within PostgreSQL to do this automagically.

Of course, the more I think about it, the more I realize this wouldn't be a property of any particular table, like a trigger or a rewrite rule is. In fact, it wouldn't even make sense to apply it to individual relations. It would have to be a re-write rule that would apply to all queries. That sounds nice, and maybe it's available in some extension, but I wouldn't be surprised if there's no native way to do this in PostgreSQL.

Solution

  • As far as I can tell, there is no way to change the meaning of an SQL query in the way you want.

    The canonical way to achieve that is by using a cursor:

    BEGIN;  -- cursors need a transaction

DECLARE c CURSOR FOR SELECT ...;

FETCH 50 FROM c;

COMMIT;

    This is a cursor in SQL, but your client API will have a way to declare cursors as well.