Geek Answers Handbook

Is there a similar function in postgresql for mysql SQL_CALC_FOUND_ROWS?

everyone who uses mysql knows:

SELECT SQL_CALC_FOUND_ROWS ..... FROM table WHERE ... LIMIT 5, 10;

and immediately after launch:

 SELECT FOUND_ROWS();

how to do it in postrgesql? so far I have only found ways in which I have to send a request twice ...

+4
source share
3 answers

No, no (at least not since July 2007 ). I'm afraid you have to resort to:

 BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT id, username, title, date FROM posts ORDER BY date DESC LIMIT 20; SELECT count(id, username, title, date) AS total FROM posts; END;

The isolation level must be SERIALIZABLE to ensure that the query does not see concurrent updates between SELECT statements.

Another option you have is to use a trigger to count rows, since they are INSERT ed or DELETE d. Suppose you have the following table:

 CREATE TABLE posts ( id SERIAL PRIMARY KEY, poster TEXT, title TEXT, time TIMESTAMPTZ DEFAULT now() ); INSERT INTO posts (poster, title) VALUES ('Alice', 'Post 1'); INSERT INTO posts (poster, title) VALUES ('Bob', 'Post 2'); INSERT INTO posts (poster, title) VALUES ('Charlie', 'Post 3');

Then follow these steps to create a table called post_count that contains the current number of rows in posts :

 -- Don't let any new posts be added while we're setting up the counter. BEGIN; LOCK TABLE posts; -- Create and initialize our post_count table. SELECT count(*) INTO TABLE post_count FROM posts; -- Create the trigger function. CREATE FUNCTION post_added_or_removed() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'DELETE' THEN UPDATE post_count SET count = count - 1; ELSIF TG_OP = 'INSERT' THEN UPDATE post_count SET count = count + 1; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- Call the trigger function any time a row is inserted. CREATE TRIGGER post_added_or_removed_tgr AFTER INSERT OR DELETE ON posts FOR EACH ROW EXECUTE PROCEDURE post_added_or_removed(); COMMIT;

Note that this maintains the current number of all rows in posts . To save the current number of specific rows, you need to configure it:

 SELECT count(*) INTO TABLE post_count FROM posts WHERE poster <> 'Bob'; CREATE FUNCTION post_added_or_removed() RETURNS TRIGGER AS $$ BEGIN -- The IF statements are nested because OR does not short circuit. IF TG_OP = 'DELETE' THEN IF OLD.poster <> 'Bob' THEN UPDATE post_count SET count = count - 1; END IF; ELSIF TG_OP = 'INSERT' THEN IF NEW.poster <> 'Bob' THEN UPDATE post_count SET count = count + 1; END IF; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;
+5
source

There is an easy way, but keep in mind that the following COGT (*) aggr function will apply to all lines returned after where and to the limit / offset (can be expensive)

SELECT I would, "count" (*) OVER () AS cnt FROM objects WHERE id> 2 OFFSET 50 LIMIT 5

+4
source

No, PostgreSQL does not try to calculate all relevant results when you need only 10 results. To count all the results, you need a separate COUNT.

0
source

More articles:


All Articles