Search code examples
postgresqlfunctionplpgsqlpostgresql-9.4record-count

Postgresql function returning number of records

I would like to create a function returning the number of records that an SQL expression passed as parameter can generate. Can anyone put me on the right path?

Solution

  • In plain SQL you can get the number of returned rows using a derived table (placing the query in a subquery in the FROM clause) like this:

    select count(*)
from (
    <your query>
    ) s;

    Do the same in a plpgsql function. You need a dynamic command as the function should work for any valid SQL query:

    create or replace function number_of_rows(query text)
returns bigint language plpgsql as $$
declare
    c bigint;
begin
    execute format('select count(*) from (%s) s', query) into c;
    return c;
end $$;

    Example: 

    select number_of_rows('select * from generate_series(1, 3)');

 number_of_rows 
----------------
              3
(1 row)