Thread: Returning SETOF INTEGER mystery...

Hello again, i found ou that no matter the function, if the return type is a
SETOF INTEGER, or a SETOF CUSTOMTYPE that includes at least ONE INTEGER
value, the error:

WARNING:  plpgsql: ERROR during compile of buscarseccion near line 6
ERROR:  missing .. at end of SQL expression

Shows up, is this a bug or am i doing something wrong?

Here goes another function that throws the same error ( I have plenty in
case you want to see them)

CREATE OR REPLACE FUNCTION BUSCarSeccion(VARCHAR) RETURNS SETOF
BusCarSeccion AS '

    DECLARE
        Libro ALIAS FOR $1;
        res BusCarSeccion;
    BEGIN
        FOR res IN select alto,altoIntercita, espacioCabeza, EspacioFin,
idColor, idColorTexto from CARSeccion                    where IdLibro =
Libro LOOP
            RETURN NEXT res;
        END LOOP;
        return;
    END;

' LANGUAGE 'plpgsql';



JuanF
(a newbie with MANY questions)

Juan Francisco Diaz <j-diaz@publicar.com> writes:
> Hello again, i found ou that no matter the function, if the return type is a
> SETOF INTEGER, or a SETOF CUSTOMTYPE that includes at least ONE INTEGER
> value, the error:

> WARNING:  plpgsql: ERROR during compile of buscarseccion near line 6
> ERROR:  missing .. at end of SQL expression

Hm.  This implies that the plpgsql parser thinks you're making a
for-loop-over-integer, which is what it will think if the name appearing
just after FOR is not a known plpgsql variable.  This doesn't seem to
be the case here though:

> CREATE OR REPLACE FUNCTION BUSCarSeccion(VARCHAR) RETURNS SETOF
> BusCarSeccion AS '
>     DECLARE
>         Libro ALIAS FOR $1;
>         res BusCarSeccion;
>     BEGIN
>         FOR res IN select alto,altoIntercita, espacioCabeza, EspacioFin,

You might be running into the problem mentioned in the "Row Types"
documentation:

: Presently, a row variable can only be declared using the %ROWTYPE
: notation; although one might expect a bare table name to work as a type
: declaration, it won't be accepted within PL/pgSQL functions.

But IIRC the symptom of this limitation isn't quite what you're seeing
...  if adding %ROWTYPE to "res BusCarSeccion" doesn't fix your problem,
could we see a complete example?

            regards, tom lane

PS: the syntax without %ROWTYPE does work as of 7.4.