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;