函数测试多个表中的行数是否大于提供的int参数 [英] Function testing whether number of rows across multiple tables is greater than supplied int param

问题描述

我正在尝试编写一个返回布尔值的PL / pgSQL函数： true ，如果多个表中的总行数大于或等于提供的值int参数，否则为 false 。

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;

推荐答案

函数中唯一唯一错误的地方是缺少

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（）从不返回NULL，因此普通加法从不失败。

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

相关：

• 用LEFT JOIN查询不返回计数为0的行

• Query with LEFT JOIN not returning rows for count of 0

合法的小写，无引号的标识符使您在Postgres中的生活更加轻松。请参阅：

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

• PostgreSQL列名是否区分大小写？

• Are PostgreSQL column names case-sensitive?

这篇关于函数测试多个表中的行数是否大于提供的int参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！