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?
2 Answers
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)
1 Comment
Jacopo Russo
Thanyou klin I followed your directions and it works perfectly! :-)
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
function F takes params K, L, N. Given table A has rows (r1, r2, r3), F(k, l, n) should return X, and F(k2, l2, n2) should return X2.