Thread: LIMIT/OFFSET doesn't work on PL/PGSQL

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

"Fernando Papa" <fpapa@claxson.com> writes:
> 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.

I think the problem is passing plpgsql variables into a cursor
declaration, not limit/offset per se.  You need to use the defined
syntax for cursor parameters to make that work, I believe.

            regards, tom lane

I resolved the LIMIT/OFFSET problem... I put all the query into a
variable and then, OPEN for EXECUTE... is not the best for the
performance I think, but it's work!

Thanks!

--
Fernando O. Papa
DBA


> -----Mensaje original-----
> De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Enviado el: viernes, 14 de marzo de 2003 19:28
> Para: Fernando Papa
> CC: pgsql-general@postgresql.org
> Asunto: Re: [GENERAL] LIMIT/OFFSET doesn't work on PL/PGSQL
>
>
> "Fernando Papa" <fpapa@claxson.com> writes:
> > 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.
>
> I think the problem is passing plpgsql variables into a
> cursor declaration, not limit/offset per se.  You need to use
> the defined syntax for cursor parameters to make that work, I believe.
>
>             regards, tom lane
>