1

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
  • Looks like a pgAdmin 4 bug. ROWS 0 is invalid – a_horse_with_no_name Dec 5 '17 at 10:15
  • 2
    Then remove it completely – a_horse_with_no_name Dec 5 '17 at 10:47
  • 1
    What @a_horse_with_no_name said. From the docs, postgresql.org/docs/current/static/sql-createfunction.html : "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." – ypercubeᵀᴹ Dec 5 '17 at 11:18
  • 1
    @yper-trollᵀᴹ: There is no better answer. If you don't want to add this as answer, the question should be closed. – Erwin Brandstetter Dec 24 '17 at 5:15
  • 1
    @ErwinBrandstetter done. – ypercubeᵀᴹ Dec 24 '17 at 10:15
2

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.