1

In plpgsql functions you can use IF (FOUND) to find how many results the previous query had. Is there an equivalent to get the number of rows from the previous query, something like IF (ROW_COUNT > 5)? Or do you always need to use a variable to store this value before checking it?

For example are these the only options?

-- option 1
select count(*) from mytable into thecount;
if (thecount > 5) ...

-- option 2
select * from mytable;
GET DIAGNOSTICS thecount = ROW_COUNT;
if (thecount > 5) ...

Have I missed any other options for getting row count of the previous query?

Which of the available options is the recommended one for getting the row count and why?

user779159
  • 9,034
  • 14
  • 59
  • 89

1 Answers1

0
if (select count(*) > 5 from mytable) then ...
if (select count(*) from mytable) > 5 then ...

or more funny

if 5 < count(*) from mytable then ...

select * from mytable; is illegal plpgsql statement so ROW_COUNT is not the option for this case.

Typically ROW_COUNT used to get the row count processed by previous statement (insert, update, delete).

Update

An if you don't need the exact count of rows but only want to check is it greater or less then some value, the more efficient way for such checks:

select count(*) > 5 from (select 1 from mytable limit 6) as t(x);

# create table mytable(id bigserial not null primary key, x numeric);
# insert into mytable(x) select random() from generate_series(1,10000000) as a;
# explain analyze select count(*) > 5 from mytable;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                  QUERY PLAN                                                                   │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Finalize Aggregate  (cost=116777.88..116777.89 rows=1 width=1) (actual time=348.815..348.815 rows=1 loops=1)                                  │
│   ->  Gather  (cost=116777.66..116777.87 rows=2 width=8) (actual time=348.713..368.395 rows=3 loops=1)                                        │
│         Workers Planned: 2                                                                                                                    │
│         Workers Launched: 2                                                                                                                   │
│         ->  Partial Aggregate  (cost=115777.66..115777.67 rows=1 width=8) (actual time=328.703..328.703 rows=1 loops=3)                       │
│               ->  Parallel Seq Scan on mytable  (cost=0.00..105361.13 rows=4166613 width=0) (actual time=0.013..211.543 rows=3333333 loops=3) │
│ Planning Time: 0.080 ms                                                                                                                       │
│ JIT:                                                                                                                                          │
│   Functions: 8                                                                                                                                │
│   Options: Inlining false, Optimization false, Expressions true, Deforming true                                                               │
│   Timing: Generation 1.422 ms, Inlining 0.000 ms, Optimization 0.484 ms, Emission 9.246 ms, Total 11.152 ms                                   │
│ Execution Time: 369.318 ms                                                                                                                    │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Time: 369,740 ms

# explain analyze select count(*) > 5 from (select 1 from mytable limit 6) as t(x);
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                       QUERY PLAN                                                       │
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate  (cost=0.17..0.19 rows=1 width=1) (actual time=0.020..0.020 rows=1 loops=1)                                  │
│   ->  Limit  (cost=0.00..0.10 rows=6 width=4) (actual time=0.013..0.015 rows=6 loops=1)                                │
│         ->  Seq Scan on mytable  (cost=0.00..163693.71 rows=9999871 width=4) (actual time=0.012..0.013 rows=6 loops=1) │
│ Planning Time: 0.104 ms                                                                                                │
│ Execution Time: 0.046 ms                                                                                               │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Time: 0,507 ms
Abelisto
  • 14,826
  • 2
  • 33
  • 41