PostgreSQL Cursor Error

Question

I have the code:

 DECLARE 
      cliente_cursor CURSOR FOR SELECT * FROM cliente;
      cliente cliente.id_clie%TYPE;
      nom cliente.nom_clie%TYPE;  
    BEGIN
      OPEN cliente_cursor;
      FETCH cliente_cursor INTO cliente, nom;

But I cannot run it. The following error appears:

ERROR: syntax error at or near "cliente"
LINE 3: cliente cliente.id_clie% TYPE;
        ^

I have the table "cliente" which has:

Table cliente

I want to create a cursor that shows only the content of the columns: id_clie, nom_clie of the previous table.

I have also used:

create or replace function facturas_cliente()
  returns table (a int, b character varying(40))
  language plpgsql as $$
declare 
  x cliente%rowtype ;
  cliente_cursor cursor for select id_clie, nom_clie from cliente 
  order by id_clie;
begin
  for x in cliente_cursor loop
    return next x;
  end loop;
end $$;

But I get the error:

ERROR: RETURN NEXT can not have parameters in a function with OUT parameters
LINE 9: return next x;
                    ^

What am I doing wrong?

Pavel Stehule · Accepted Answer · 2015-01-19 06:58:03Z

1

First issue is strange. I tested on PostgreSQL 9.5 (but same code should to work on 9.2 and newer):

CREATE TABLE cliente(id_clie int, tel_clie varchar(15), dir_clie varchar(15));

DO $$
DECLARE
  cliente_cursor CURSOR FOR SELECT * FROM cliente;
  cliente cliente.id_clie%TYPE;
  tel cliente.tel_clie%TYPE;
BEGIN
  OPEN cliente_cursor;
END;
$$;

And it works without any issue. It looks like some mistyped error.

Second issue is clear. When function has a OUT variables or is declared as TABLE function, then RETURN NEXT has to be without expression. Returned composite value is based on actual context of OUT variables (columns declared in TABLE clause are OUT variables too). Your code should to be:

create or replace function facturas_cliente() 
returns table (a int, b character varying(40))
language plpgsql as $$
declare 
  cliente_cursor cursor for select id_clie, nom_clie 
                            from cliente 
                            order by id_clie;
  r record;
begin
  for r in cliente_cursor -- a,b ~ OUT var declared in TABLE() clause
  loop
    a := r.id_clie; b := r.nom_clie; 
    return next;  -- not: return next x;
  end loop;
end $$;

This code can be reduced in PL/pgSQL two ways:

use a SQL function

create or replace function facturas_cliente() 
returns table (a int, b character varying(40))
language sql as $$
  select id_clie, nom_clie from cliente order by id_clie;
$$ language plpgsql;

use a RETURN QUERY statement in plpgsql:

create or replace function facturas_cliente() 
returns table (a int, b character varying(40))
language sql as $$
BEGIN
   RETURN QUERY SELECT id_clie, nom_clie 
                FROM cliente 
                ORDER BY id_clie;
   RETURN;
END;
$$ language plpgsql;

Attention: these function can block a SQL optimizer if you use it in some complex query than trivial. Personally I don't like it. Use a view instead. It works like you need and there is no risk with optimization.

CREATE VIEW facturas_cliente
   AS SELECT id_clie, nom_clie 
      FROM cliente 
      ORDER BY id_clie;

edited Jan 19, 2015 at 6:58

answered Jan 18, 2015 at 15:06

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Pérez Over a year ago

Thanks. I tried the second clode of block but it shows an error regarding to the for loop. ERROR: a FOR loop of a cursor must have only one target variable

Pavel Stehule Over a year ago

hmm - a artific limit for compatibility with PL/SQL :/. I forgot it. Fixed

Collectives™ on Stack Overflow

PostgreSQL Cursor Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related