postgresql中是否有类似的函数用于mysql的SQL_CALC_FOUND_ROWS?
发布时间:2020-12-13 16:04:38 所属栏目:百科 来源:网络整理
导读:每个使用 mysql的人都知道: SELECT SQL_CALC_FOUND_ROWS ..... FROM table WHERE ... LIMIT 5,10; 并在运行之后: SELECT FOUND_ROWS(); 我怎么在postrgesql中这样做?到目前为止,我发现只有两次我必须发送查询… 解决方法 不,没有(至少 not as of July 200
每个使用
mysql的人都知道:
SELECT SQL_CALC_FOUND_ROWS ..... FROM table WHERE ... LIMIT 5,10; 并在运行之后: SELECT FOUND_ROWS(); 我怎么在postrgesql中这样做?到目前为止,我发现只有两次我必须发送查询… 解决方法
不,没有(至少
not as of July 2007).我担心你不得不诉诸:
BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT id,username,title,date FROM posts ORDER BY date DESC LIMIT 20; SELECT count(id,date) AS total FROM posts; END; isolation level需要是SERIALIZABLE以确保查询不会在SELECT语句之间看到并发更新. 但是,另一个选择是使用触发器在行被插入或删除时对行进行计数.假设您有以下表格: 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'); 然后,执行以下操作以创建一个名为post_count的表,其中包含帖子中行数的运行计数: -- 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; 请注意,这会保留帖子中所有行的运行计数.要保持某些行的运行计数,您必须调整它: 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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |