Linked Questions

Popular Questions

loop function until limit is meet postgres

Asked by At

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.

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.

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.

CREATE OR REPLACE FUNCTION getJournalistTopicItems() RETURNS  TEXT AS $$
DECLARE
    currentCount INTEGER = 0;
    currentItemIds int[];
    latestItem INTEGER = 0;
BEGIN       
        WHILE currentCount < 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;

The function getFilteredItems looks like this:

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 > latestId
         limit 1000)
         SELECT (SELECT id FROM fItems order by id desc limit 1), fItems.id FROM fItems;
END;
$$ LANGUAGE plpgsql;

The error i get when calling getJournalistTopicItems is this:

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

LATEST UPDATE:

CREATE OR REPLACE FUNCTION getJournalistTopicItems() RETURNS  SETOF TEXT AS $func$
DECLARE
    idarr int[];
    idmed int[];
    latestItem INTEGER = 0;
    currentCount INTEGER = 0;
BEGIN       
        WHILE currentCount < 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;

Related Questions