title: loop function until limit is meet postgres
body: <p>I have a pretty large table where i want to start from the lowest id and loop through and filter until i get 1000 rows which i then want to return.</p>
  
  <p>So my function getFilteredItems will do some filtering and then return 1000 rows. It has a parameter of latestItemId so that it will take 1000 rows after a specific id. getFilteredItems is then called by getJournalistTopicItems that filteres even more. Most of the time this function will filter away all rows hence i want to loop through untill i eventually get 1000 rows when all the filtering is done. </p>
  
  <p>I am super-noob at sql and specifically postgres so all this might been overkill or totaly wrong but this is what i've done so far.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION getJournalistTopicItems() RETURNS  TEXT AS $$
  DECLARE
      currentCount INTEGER = 0;
      currentItemIds int[];
      latestItem INTEGER = 0;
  BEGIN       
          WHILE currentCount &lt; 1000 LOOP      
              WITH fItems AS (
                  SELECT * FROM getFilteredItems(latestItem)
              )
              SELECT fi.itemId, ift.content
              FROM fItems fi
              INNER JOIN journalistitems ji ON ji.itemid = fi.itemid       
              INNER JOIN itemfulltexts ift ON ji.itemid = ift.itemid
              INNER JOIN journalists j ON j.id = ji.journalistid
              INNER JOIN contacts c ON c.journalistid = j.id
                WHERE  c.isprivate = FALSE
                AND c.disabled = FALSE
                AND c.verificationstatus = 1;           
  
              currentItemIds := array_append(fi.itemId);
              currentCount := array_length(currentItemIds);
              SELECT latestItemId INTO latestItem FROM getFilteredItems(latestItem) limit 1;
  
          END LOOP;
          RETURN array_to_string(currentItemIds);
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>The function getFilteredItems looks like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION getFilteredItems(latestId INTEGER) RETURNS TABLE(
      latestItemId integer,
      itemid integer
  ) AS $$
  BEGIN
      RETURN QUERY
      WITH fItems AS(select i.id 
          from items i
          inner join articlemetadatas am on am.itemid = i.id
          where NOT EXISTS (SELECT 1 FROM journalistitemcategories jic WHERE jic.itemid = i.id)
            AND NOT EXISTS (SELECT 1 FROM topicitemwords tw WHERE tw.itemid = i.id)
            AND i.languagecode in ('sv')
            AND am.contentprotected != 0
            AND i.id &gt; latestId
           limit 1000)
           SELECT (SELECT id FROM fItems order by id desc limit 1), fItems.id FROM fItems;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>The error i get when calling getJournalistTopicItems is this:</p>
  
  <pre><code>ERROR:  query has no destination for result data
  HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
  CONTEXT:  PL/pgSQL function getjournalisttopicitems() line 8 at SQL statement
  SQL state: 42601
  </code></pre>
  
  <p>LATEST UPDATE:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION getJournalistTopicItems() RETURNS  SETOF TEXT AS $func$
  DECLARE
      idarr int[];
      idmed int[];
      latestItem INTEGER = 0;
      currentCount INTEGER = 0;
  BEGIN       
          WHILE currentCount &lt; 1000 LOOP      
              WITH fItems AS (
                  SELECT * FROM getFilteredItems(latestItem)
              )
              SELECT fi.itemId, ift.content
              FROM fItems fi
              INNER JOIN journalistitems ji ON ji.itemid = fi.itemid       
              INNER JOIN itemfulltexts ift ON ji.itemid = ift.itemid
              INNER JOIN journalists j ON j.id = ji.journalistid
              INNER JOIN contacts c ON c.journalistid = j.id
                WHERE  c.isprivate = FALSE
                AND c.disabled = FALSE
                AND c.verificationstatus = 1;           
  
              idmed := (select array_agg(itemId) from fItems);
              idarr := (select idarr || idmed);
              currentCount := (select array_length(currentCount, int));
              latestItem := (select latestItem FROM getFilteredItems(latestItem) limit 1);        
  
          END LOOP;
          RETURN QUERY SELECT array_to_string(idarr, ',');
  END;
  $func$ language plpgsql;
  </code></pre>