check for number of rows in a postgresql function

Question

How can I check if the first query returns data, and if yes return this data or search in another table? Something like:

CREATE OR REPLACE FUNCTION finddata() RETURNS TABLE (content text) AS $$
BEGIN
  # now pseudocode
  x = SELECT foo FROM bar WHERE ....;
  if num_rows(x) > 0
    return x
  else
    RETURN QUERY SELECT foo FROM bar2 WHERE ....;
END;
$$ LANGUAGE plpgsql;

Thanks Klaus

Do you want to know if it's query1 or query2, or you just want the results from both ? If you want the results you can do an union or a join with some CASE/WHEN in the select. No need of procedure then. — Daniel E.
– Daniel E., Commented Feb 21, 2019 at 10:37
I do not want a union. The function should return the data either from table bar, or if not found in bar, then from table bar2. — Klaus Darilion
– Klaus Darilion, Commented Feb 21, 2019 at 10:40

JosMac · Accepted Answer · 2019-02-21 10:42:32Z

2

You can do it in easier way without checking if data exists - I already use this construct in more functions with success (function does not end after first RETURN QUERY - you can have several of them in sequence in function as far as they all return the same structure - I also use it when necessary):

CREATE OR REPLACE FUNCTION finddata() RETURNS TABLE (content text) AS $$
DECLARE
  cnt int;
BEGIN
  RETURN QUERY SELECT foo FROM bar WHERE ....;
  GET DIAGNOSTICS cnt = ROW_COUNT;
  if cnt = 0 then
    RETURN QUERY SELECT foo FROM bar2 WHERE ....;
  end if;
END;
$$ LANGUAGE plpgsql;

answered Feb 21, 2019 at 10:42

JosMac

2,3601 gold badge22 silver badges25 bronze badges

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

Comments

Kaushik Nayak · Accepted Answer · 2019-02-21 10:36:13Z

0

use IF EXISTS

CREATE OR REPLACE FUNCTION finddata() 
RETURNS TABLE (content text) AS $$
  BEGIN
  IF EXISTS ( SELECT foo FROM bar WHERE .... )
   THEN
    return QUERY SELECT foo FROM bar;
  else
    RETURN QUERY SELECT foo FROM bar2 WHERE ....;
END;
$$ LANGUAGE plpgsql;

answered Feb 21, 2019 at 10:36

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

check for number of rows in a postgresql function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related