Function testing whether number of rows across multiple tables is greater than supplied int param

Question

I'm trying to write an PL/pgSQL function that returns a boolean: true, if the total number of rows across multiple tables is greater or equal than the supplied int parameter, else false. But not having much luck.

CREATE OR REPLACE FUNCTION allrecordsHaveUpdated (numberOfRecords int)
RETURNS boolean AS $$
declare
    totalRecords integer;
    recordsInserted boolean := false;

BEGIN
    totalRecords = (select SUM(row_count)
    from (
        SELECT count(*) as row_count
        FROM "table_1"
        union all
        SELECT count(*) as row_count
        FROM "table_2"
        union all
        SELECT count(*) as row_count
        FROM "table_3"
    ) as total)

    IF totalRecords >= numberOfRecords THEN
        recordsInserted = true;
    END IF;
    RETURN recordsInserted;
END;
$$ LANGUAGE plpgsql;

Seems like your function should work fine. Don't forget to add ```semicolon``` after ```total)``` — Abdusoli, Jul 21 '20 at 07:07

Erwin Brandstetter · Answer 1 · 2020-07-22T03:39:04.060

The only thing strictly wrong in your function is the missing semicolon after the first assignment.

But the whole thing is overblown. Burns down to just:

CREATE OR REPLACE FUNCTION all_records_updated (_number_of_records int)
  RETURNS boolean
  LANGUAGE sql STABLE STRICT AS
$func$
SELECT (SELECT count(*) FROM table_1)
     + (SELECT count(*) FROM table_2)
     + (SELECT count(*) FROM table_3) >= _number_of_records
$func$;

count() never returns NULL, so the plain addition never fails.

Query with LEFT JOIN not returning rows for count of 0

Legal, lower-case, unquoted identifiers make your life in Postgres easier. See:

Are PostgreSQL column names case-sensitive?

score 0 · Answer 2 · answered Jul 21 '20 at 15:43

Try a SQL function rather than a plpgsql function:

create or replace function allrecordshaveupdated (numberofrecords int)
 returns boolean 
 language sql
as $$
    select sum(row_count) > numberofrecords
      from (select count(*) as row_count from "table_1" 
            union all
            select count(*) from "table_2" 
            union all
            select count(*) from "table_3" 
          ) as total;
 $$;

Function testing whether number of rows across multiple tables is greater than supplied int param

2 Answers2

Linked