CODEWITHSUNDEEP
sqlpostgresqlpostgresql-13
Romillion
Romillion

Reputation: 159

postgresql FUNCTION: If Select returns nothing then do another Select

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;

Upvotes: 1

Views: 728

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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

Upvotes: 1

Related Questions