postgresql FUNCTION: If Select returns nothing then do another Select

Question

Please help me to create postgresql FUNCTION.

I have two queries:

If the first one does not return a value

SELECT * FROM event e
    WHERE e.organizer_id IN (SELECT u.id FROM "user" u WHERE u.school_id = 9)
    OR e.id IN (SELECT i.event_id FROM invite i WHERE i.user_id IN (SELECT u.id FROM "user" u WHERE u.school_id = 9));

then I need to call the second query

SELECT * FROM event e 
    INNER JOIN user u on u.id = e.organizer_id  
    INNER JOIN school s on u.school_id = s.id
WHERE u.school_id = :schoolId and ST_DWithin(ST_Transform(e.geom, 2163), ST_Transform(s.geom,2163), :radius * 1609.34)

I tried to create a function but got stuck. I don't have so much experience in SQL, please help.

This is how i think:

CREATE FUNCTION all_nearby_events(mySchoolID INT) RETURNS event AS $$
DECLARE
    user_ids bigserial; -- save List of user ids because i need it twice
BEGIN
    user_ids :=(SELECT u.id FROM "user" u WHERE u.school_id = mySchoolID);

    SELECT * FROM event e
                      INNER JOIN user u on u.id = e.organizer_id
                      INNER JOIN school s on u.school_id = s.id
    WHERE u.school_id = :schoolId and ST_DWithin(ST_Transform(e.geom, 2163), ST_Transform(s.geom,2163), :radius * 1609.34)

      and not exists (
            SELECT * FROM event e WHERE e.organizer_id IN (user_ids) OR e.id IN (SELECT i.event_id FROM invite i WHERE i.user_id IN (user_ids))
        )
    union all
    SELECT * FROM event e WHERE e.organizer_id IN (user_ids) OR e.id IN (SELECT i.event_id FROM invite i WHERE i.user_id IN (user_ids));
END;
$$
LANGUAGE plpgsql;

Stefanov.sm · Accepted Answer · 2021-09-27 07:53:31Z

1

Use return query and check the found built-in variable.
Please note returns SETOF. I assume that your queries are fine and copy them more or less unchanged except for myschoolid and max_radius arguments.

create or replace function all_nearby_events(myschoolid integer, max_radius numeric)
returns SETOF event as
$$
begin

 return query -- your first query follows
    SELECT * FROM event e
    WHERE e.organizer_id IN (SELECT u.id FROM "user" u WHERE u.school_id = myschoolid)
    OR e.id IN (SELECT i.event_id FROM invite i WHERE i.user_id IN (SELECT u.id FROM "user" u WHERE u.school_id = myschoolid));

 if found then return; end if;

 return query -- your second query follows
    SELECT * FROM event e 
    INNER JOIN user u on u.id = e.organizer_id  
    INNER JOIN school s on u.school_id = s.id
    WHERE u.school_id = myschoolid and ST_DWithin(ST_Transform(e.geom, 2163), ST_Transform(s.geom,2163), max_radius * 1609.34);

 -- more queries can follow here

end;
$$ language plpgsql;

Edit
Here is a suggestion (suboptimal though) how you could do it without a plpgsql function.

with t as
(
 select 
   1 as query_order, event.* 
   -- the rest of your first query here
 union all
   2 as query_order, event.* 
   -- the rest of your second query here

   -- more queries can follow here
)
select * from t 
where query_order = (select min(query_order) from t);

edited Sep 27, 2021 at 7:53

answered Sep 26, 2021 at 22:38

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Romillion Over a year ago

Thanks for the answer. Another question for experience. Is it possible to solve this problem without a procedure? I mean to combine these two SELECT into one.

Romillion Over a year ago

Can you please check FINAL block ?

Stefanov.sm Over a year ago

What is the FINAL block?

Romillion Over a year ago

I updated your answer and added SQL marked as bold FINAL

Stefanov.sm Over a year ago

I see. Unfortunately it is not valid SQL. It should be ` ... select * from t WHERE t.name ~* 'event title string' LIMIT 1000 OFFSET 0`. Pls. note that I am not looking into your business logic.

|

Collectives™ on Stack Overflow

postgresql FUNCTION: If Select returns nothing then do another Select

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related