ERROR: OFFSET must not be negative in PostgreSQL
Dung Do Tien
Feb 03 2021
340
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.
-
P0
Peter Melling Feb 03 2021
The Index of the table in PostgreSQL must starts from 0. You can not OFFSET from -1 record.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.