How to retrieve row count in a RETURN QUERY statement with postgres?-CodePudding

I need to get N records from a table and the query I need to execute might return less record than N. Therefore, I'd like to execute the query multiple times until I have exactly N records. I was messing around with table functions and I had something like this:

CREATE OR REPLACE FUNCTION make_dataset(n INTEGER) 
RETURNS SETOF data
AS $$
BEGIN
  WHILE $1 > 0
    RETURN QUERY SELECT * FROM data LIMIT $1;
    -- How to update n from the amount of rows returned by the above?
  END LOOP;
  RETURN;
END $$ 
LANGUAGE plpgsql;

However, I don't know how to update n based on the amount of rows returned by the query. Is there any way to achieve this?

Note: The query has been simplified for brievety. It's not a SELECT * and might return less row than expected, hence why I'm looking at table functions with a loop.

CodePudding user response：

Use GET DIAGNOSTICS.

CREATE OR REPLACE FUNCTION make_dataset(n INTEGER) 
RETURNS SETOF my_table
AS $$
DECLARE i integer;
BEGIN
  WHILE n > 0 LOOP
    RETURN QUERY SELECT * FROM my_table LIMIT n;
    GET DIAGNOSTICS i:= ROW_COUNT;
    n:= n- i;
  END LOOP;
  RETURN;
END $$ 
LANGUAGE plpgsql;