Postgresql – ERROR: ROWS must be positive

pgadmin-4plpgsqlpostgresql

Why the error when I run the below in pgAdmin 4 query editor? I copied the create statement in one database and trying to move to a new server.

So I right clicked the function in pgAdmin and selected from the menu Scripts –> CREATE Script the generate the below code. Copied and executed into a new database.

-- FUNCTION: public.osmtrip_format(bigint, numeric, numeric)

-- DROP FUNCTION public.osmtrip_format(bigint, numeric, numeric);

CREATE OR REPLACE FUNCTION public.osmtrip_format(
    num bigint,
    length numeric,
    tolerance numeric)
RETURNS bigint
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE 
    ROWS 0
AS $BODY$

    SELECT
        CASE WHEN $1=0 OR $2<=0.7*$3 THEN NULL ELSE $1 END;

$BODY$;

ALTER FUNCTION public.osmtrip_format(bigint, numeric, numeric)
    OWNER TO gis;

ERROR:  ROWS must be positive
SQL state: 22023

Best Answer

From the documentation about CREATE FUNCTION:

result_rows
A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows."

ROWS 0 is not allowed even in those functions (should be positive).

Since the function is not returning a set, you need to remove that ROWS n.

Related Question