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

Question

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.

klin · Accepted Answer · 2022-09-28 15:19:14Z

1

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;

answered Sep 28, 2022 at 15:19

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related