PostgreSQL Function Return from Second Query if First Query is Empty

Question

I have a PostgreSQL 11x environment in Windows. I am trying to figure out syntax for a Function which returns results of a query. The first query in the code below works much better most of the time but if it fails to return something then I'd like to use the second query's return--which is much slower but at least has some data being returned. Here is my Function:

CREATE OR REPLACE FUNCTION public.myfunction()
RETURNS TABLE(start_vid bigint, end_vid bigint, agg_cost double precision)
LANGUAGE plpgsql
AS $function$
DECLARE 
--declarations
BEGIN   
 RETURN QUERY
 SELECT  * FROM pgr_dijkstraCost(
        'SELECT gid as id, * FROM ways  as r, 
        (SELECT ST_Expand(ST_Extent(the_geom), ' || box_size || ') as box  FROM ways as edge_table    
        WHERE edge_table.source = ' || VT1 || ' OR edge_table.target = ' || VT2 || ') as box
        WHERE r.the_geom && box.box',
        VT1, VT2, arg_directed_flag::boolean
RETURN QUERY
    SELECT * FROM pgr_dijkstraCost( 'SELECT gid as id, * FROM ways', VT1, VT2, 
   arg_directed_flag::boolean); 
END
$function$

Note: I want to execute the second SELECT only if the first Select doesn't return anything. Can't find any code for PostgreSQL to modify my function and fairly new to PostgreSQL environment.

Thank you!

It looks like you could restructure your query into a LEFT JOIN ... — wildplasser
– wildplasser, Commented Mar 24, 2021 at 14:43
Thank you. I have accepted an Answer otherwise would give it a try. — IrfanClemson
– IrfanClemson, Commented Mar 24, 2021 at 14:48
You really should give it a try, your query is barely readable, IMHO. It could cost you 15 minutes now, but it could save hours of maintenance work. Also: try to get rid of the string concatenation; it is a disaster waiting to happen. — wildplasser
– wildplasser, Commented Mar 24, 2021 at 14:54
But won't LEFT JOIN make the second SELECT query to execute? That's what I am trying to desperate avoid because the second select is very slow and is only a fall back plan. Thank you. — IrfanClemson
– IrfanClemson, Commented Mar 24, 2021 at 16:19
Basically, I cannot read (and understand) your query. Why do you need the scalar subquery? Is it even correlated? Why do you use all these SELECT *s ? And the second query is slow because it retrieves the complete ways table, which probably is quite large. — wildplasser
– wildplasser, Commented Mar 24, 2021 at 16:34

JGH · Accepted Answer · 2021-03-24 14:19:32Z

1

You can use the FOUND variable, which is true if the previous query returned at least one row

IF (FOUND = FALSE) THEN
 RETURN QUERY
    SELECT * FROM pgr_dijkstraCost( 'SELECT gid as id, * FROM ways', VT1, VT2, 
   arg_directed_flag::boolean); 
END IF;

answered Mar 24, 2021 at 14:19

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

2 Comments

IrfanClemson Over a year ago

Thank you. Let me see if I can figure this out in my code...

IrfanClemson Over a year ago

Wow! All I had to do was to wrap my second RETURN QUERY surrounded by the IF statement you sent me and voila!! Thank you so much!!

Collectives™ on Stack Overflow

PostgreSQL Function Return from Second Query if First Query is Empty

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related