Union 因 JSON 列而崩溃，显然，临时表在 PostgreSQL 函数中几乎没有用。
我来自 MS SQL Server 世界，似乎我在 PostgreSQL 中尝试的一切都是错误的。这就是我想要做的。有人可以告诉我最好的方法吗？或者有什么办法可以做到这一点？
create or replace function getNextStories( previous_id bigint )
returns setof output_stories as
$$
create temp table output_stories (
"id" bigint,
submitted_date timestamp with time zone,
author character varying,
title character varying,
"content" json,
pinned boolean
);
insert into output_stories("id", submitted_date, author, title, "content", pinned)
select "id", submitted_date, categories, author, title, "content", pinned
from Story
where Pinned = true;
insert into output_stories("id", submitted_date, author, title, "content", pinned)
select "id", submitted_date, categories, author, "content", title, pinned
from Story
where Pinned = false
and "id" < previous_id
order by "id" desc
limit 20;
select * from output_stories
$$
language 'sql' ;
我尝试了很多不同的事情，每次我都会遇到错误。这个特殊的尝试返回了这个。
ERROR: type "output_stories" does not exist
********** Error **********
ERROR: type "output_stories" does not exist
SQL state: 42704
如果我正确理解您的用例，您想选择所有固定的故事和一些参考 ID 后的（按 ID）前 20 个非固定的故事。除非我忽略了您问题中的某些内容，否则在我看来您根本不需要存储函数：
(
SELECT id, title, body, pinned FROM Story
WHERE (id > 4 and not pinned) ORDER BY id DESC LIMIT 20
)
UNION ALL
SELECT id, title, body, pinned FROM Story WHERE pinned;
我用一堆测试做了一个sqlfiddle。以供参考：
-- minimal example table
CREATE TABLE story (
id BIGINT PRIMARY KEY,
title VARCHAR,
body JSON,
pinned BOOLEAN
)
-- minimal example records
INSERT INTO story (id, title, pinned) VALUES
(1, 'pinned', true),
(2, 'not pinned', false),
(4, 'previous id', false),
(5, 'next 1', false),
(6, 'next 2', false),
(7, 'next 3', true),
(8, 'next 4', false);
-- expectation
-- 1 INCLUDE (pinned)
-- 2 OMIT (before ref ID)
-- 4 OMIT (is ref ID)
-- 5 OMIT (after ref ID, but outside limit 2)
-- 6 INCLUDE (after ref ID, not pinned)
-- 7 INCLUDE (pinned, but don't duplicate!)
-- 8 INCLUDE (after ref ID, not pinned)
-- Query
(
SELECT id, title, body, pinned FROM Story
WHERE (id > 4 and not pinned) ORDER BY id DESC LIMIT 2
)
UNION ALL
SELECT id, title, body, pinned FROM Story WHERE pinned;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句