From: | "Fernando Papa" <fpapa(at)claxson(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | LIMIT/OFFSET doesn't work on PL/PGSQL |
Date: | 2003-03-14 21:16:42 |
Message-ID: | CB94A4924490EC4A81EDA55BA378B7BAFE7979@exch2k01.buehuergo.corp.claxson.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I try to limit the amount of rows retrieved (on a message base), I need
to do some kind of "pages" with X messages each one... so I create these
function than retrieve messages from user (ID_user_in), the amount of
messages by page (IN_cant_pag) and the offset (to skip messages from
prev pages).
I don't know why the last query doesn't work inside function... I run
the query from psql and every goes ok, but when I use the function, I
can't see anything (the function doesn't retrieve anything). I put fixed
value on LIMIT and the function execute correctly.
The problem is than I need to use a variable to LIMIT.
Any ideas why doesn't work?
Thanks a lot!
CREATE OR REPLACE FUNCTION messages_rec(
,NUMERIC,NUMERIC,NUMERIC,NUMERIC,refcursor ) RETURNS refcursor AS '
DECLARE
ID_instalation_in alias for $1;
ID_user_in alias for $2;
OFF_set_in alias for $3;
IN_cant_pag alias for $4;
OUT_cursor alias for $5;
OPEN OUT_cursor FOR
SELECT m.id_mensaje
,m.fecha
,m.id_mensaje_operador
,m.contenido
,m.tema
,m.id_tipo_mensaje
,m.id_usuario
,m.id_instalacion
,u.username
,m.leido
FROM
men_mensajes m
,core_usuarios u
WHERE m.id_usuario_destinatario = ID_user_in
AND m.id_instalacion_destinatario = ID_instalation_in
AND upper(m.id_tipo_mensaje) in (''OT'',''UT'')
AND upper(m.estado_origen) in (''OK'',''BO'')
AND upper(m.estado_destino) = ''OK''
AND m.id_usuario = u.id_usuario
AND u.id_instalacion = m.id_instalacion
ORDER BY leido,fecha desc
LIMIT IN_cant_pag
OFFSET OFF_set_in
;
RETURN OUT_cursor;
--
Fernando O. Papa
DBA
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Reyes | 2003-03-14 21:17:26 | Re: Corrupted sequences |
Previous Message | Dennis Gearon | 2003-03-14 21:12:34 | Re: Corrupted sequences |