How to return multiple default-values, when no results found in postgres?

Question

I have the following function on a postgres-database:

CREATE OR REPLACE FUNCTION getNearestPoints(float[], float[], float[], float[], int)       
                    RETURNS SETOF record AS $$                                                                      
                DECLARE                                                                                             
                    i INT;                                                                                          
                    ret RECORD;     
                BEGIN                                                                                               
                    FOR i IN 1..$5 LOOP                                                                             
                         FOR ret IN EXECUTE 'SELECT height, ST_Y(point), ST_X(point)  FROM heights                   
                         WHERE ST_Intersects(ST_MakeEnvelope(' || $1[i] || ','  || $2[i] || ',' || $3[i] || ',' || $4[i] || ', 4326), point)' LOOP 
                         RETURN NEXT ret;                                                                           
                         END LOOP;                                                                                  
                    END LOOP;                                                                                       
                    RETURN;                                                                                         
                END;                                                                                                
                $$ LANGUAGE plpgsql;

I want to return default values to the record for each column (e.g. (-2000, -181, -181)), if the query returns no rows.

Coalesce with records doesn't seem to be possible.

How can I manage to do this?

Wojtas · Accepted Answer · 2012-12-11 12:37:39Z

1

If nothing was found then before RETURN you can assign values to the variable ret:

...
if not found then
    ret = row(-2000::integer, -181::integer, -181::integer);
    return next ret;
end if;

return;
...

Make sure that types used with row - "::integer" are the same as those in:

SELECT height, ST_Y(point), ST_X(point)  FROM heights

answered Dec 11, 2012 at 12:37

Wojtas

2,3641 gold badge17 silver badges9 bronze badges

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

3 Comments

Martin Over a year ago

Thanks that sounds good. But how can I evaluate, if no rows were returned (without executung the query again and count the results)?

Wojtas Over a year ago

Inside a function body you can use build in variable found which is used in the example above. It determins if in the last statment result was found.

Martin Over a year ago

Thanks a lot! It works. Sorry, I thought, it would just be an example to show the method.

Collectives™ on Stack Overflow

How to return multiple default-values, when no results found in postgres?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related