Postgresql function returning number of records

Question

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?

Answer 1

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) 

Answer 2

Your function should return a SETOF some (record) type. You then use the RETURN NEXT syntax to return each row.

See the example in the doc page for this. https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#id-1.8.8.8.3.4