ERROR: OFFSET must not be negative in PostgreSQL

Dung Do Tien Feb 03 2021 362

In the PostgreSQL database, I create a function as below:

CREATE OR REPLACE FUNCTION public.func_fe_lead_getlist (
  _pagesize integer,
  _pageindex integer
)
RETURNS TABLE (
  id integer,
  title varchar,
  sapo varchar,
  avatar varchar,
  type smallint,
  status smallint
) AS
$body$

DECLARE _offsetindex = _pagesize * (_pageindex - 1);
BEGIN
   SELECT * from leadactions limit _pagesize OFFSET _offsetindex
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

And I try to execute that function to get data:

 SELECT * FROM func_fe_lead_getlist(15, -1)

But I got an error : 2201X: OFFSET must not be negative.

"Npgsql.PostgresException (0x80004005): 2201X: OFFSET must not be negative at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() --- 
End of stack trace from previous location where exception was thrown --- 
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() --- 
End of stack trace from previous location where exception was thrown --- 
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) 
at Npgsql.NpgsqlDataReader.NextResult() 
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) 
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at DVG.AutoPortal.InfrastructureLayer.Databases.PostgreDb.Helpers.PostgreDalHelper.QuerySP[T](String spName, Object condition) in /src/DVG.AutoPortal.InfrastructureLayer/Databases/PostgreDb/Helpers/PostgreDalHelper.cs:line 320 
Severity: ERROR;SqlState: 2201X;Code: 2201X;MessageText: OFFSET must not be negative;Where: PL/pgSQL function func_fe_newcar_getlist_20201201(integer,double precision,double precision,integer,smallint,integer,integer,boolean,integer,integer,integer,timestamp without time zone) line 137 
at RETURN QUERY;File: nodeLimit.c;Line: 253;Routine: recompute_limits"
Have 1 answer(s) found.
  • P

    Peter Melling Feb 03 2021

    The Index of the table in PostgreSQL must starts from 0. You can not OFFSET from -1 record. 

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close