CODEWITHSUNDEEP
postgresqllimit
nachtnegall
nachtnegall

Reputation: 3

Using dynamically limit in postgresql

I want to use

select * from table limit x;

This x can change dynamically. I don't want to use number. There are a solution for limit that use parameter?

Upvotes: 0

Views: 1174

Answers (2)

raphael
raphael

Reputation: 2823

To further expand on Richard Huxton's answer, this could be used in a LATERAL sub-query, for example to dynamically limit a K-Nearest Neighbours query:

SELECT loc_id, num_neighbours, neighbour_id
FROM locations,
LATERAL (SELECT neighbour_id 
         FROM neighbours 
         ORDER BY ST_distance(locations.geom, neighbours.geom) 
         LIMIT num_neighbours) knn

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

Sure you can. Never needed to myself but:

SELECT * FROM some_table LIMIT (SELECT a_limit FROM other_table);

Obviously that sub-query should only return one row.

Upvotes: 2

Related Questions