Geek Answers Handbook

What is the meaning of this postgres error: "ROWS is not applicable when a function does not return a set"

I am new to postgres and getting an error message that I cannot find in Stackoverflow (?). I am trying to write a function that returns true when a person has the right to the task, and false - to the absence. This causes "ERROR: ROWS is not applicable when the function does not return a set." Found something in Spanish - but nothing in SO. Can someone explain this?

CREATE OR REPLACE FUNCTION "isPersonQualifiedForJob"(pid integer, jid integer) RETURNS bit AS ' IF (SELECT count(*) FROM "getSkillsForJob"("jid") "j" WHERE NOT EXISTS ( SELECT 1 FROM "getSkillsForPerson"("pid") "p" WHERE "j"."SkillID"="p"."SkillID" ) )> 0 THEN return 0; ELSE return 1; END IF; ' LANGUAGE sql VOLATILE COST 100 ROWS 1000; ALTER FUNCTION "isPersonQualifiedForJob"(integer) OWNER TO postgres;
+4
source share
2 answers

In this line:

 ROWS 1000;

you tell the scheduler that you expect about 1000 lines to return from the function, but only one value is declared as returned, bit:

 RETURNS bit AS
+6
source

The message is pretty clear, and the docs are :

ROWS result_rows

A positive number giving the estimated number of rows that the scheduler should expect to return. This is only allowed when a function is declared to return a set. The default value is 1000 lines.

Your function does not return a rowset, but one logical one, so I don’t know why you are adding this row to the ROWS 1000 (copy-paste from another function?). Just delete it and everything will be fine.

+4
source

More articles:


All Articles