postgresql - 如何在 PostgreSQL 函数中组合两个选择？

Question

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

Answer 1

如果我正确理解您的用例，您想要选择所有固定故事和(按 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;

我做了 an 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;