How can I combine two selects in PostgreSQL function?

Question

Union blows up because of the JSON column, and apparently, temp tables are almost useless inside PostgreSQL functions.

I'm coming from the MS SQL Server world, and it seems that everything I try in PostgreSQL is wrong. This is what I am trying to do. Can someone please tell me the best way to do it? Or any way to do it for that matter?

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' ;

I have tried many different things and every way I go I get an error. This particular attempt returns this.

ERROR:  type "output_stories" does not exist
********** Error **********

ERROR: type "output_stories" does not exist
SQL state: 42704

When you write "Union blows up because of the JSON column", is this what you're running in to? dba.stackexchange.com/a/73522 — Barend
– Barend, Commented Jun 10, 2017 at 10:07
I would maybe prefer to do this in a union, yes. But I could not find any way around the union problem. If union is inefficient, then I would prefer a different direction anyway. — Danny Ellis Jr.
– Danny Ellis Jr., Commented Jun 10, 2017 at 10:12
I didn't because I didn't want duplicates, but that is something to try. — Danny Ellis Jr.
– Danny Ellis Jr., Commented Jun 10, 2017 at 10:13
Union all still blows up on the JSON column. Good suggestion though. — Danny Ellis Jr.
– Danny Ellis Jr., Commented Jun 10, 2017 at 10:18

Barend · Accepted Answer · 2017-06-10 10:46:05Z

If I understand your use case correctly, you want to select all pinned stories and the (by ID) first 20 non-pinned stories after some reference id. Unless I'm overlooking something in your question, it seems to me that you don't need a stored function at all:

(
    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;

I made an sqlfiddle with a bunch of tests. For reference:

-- 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;

Collectives™ on Stack Overflow

How can I combine two selects in PostgreSQL function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related