Thread: Why this does not work ??

Hello all, why this does not work ?

-----------------------------------------
create table original(num int);
-----------------------------------------
create table copia(num int);
-----------------------------------------
insert into original(num) values (1);
insert into original(num) values (2);
insert into original(num) values (3);
-----------------------------------------
create function copiar()
    returns int as '
    declare
        ponteiro int;
    begin
        for ponteiro in select * from original order by num asc loop
            insert into copia(num) values(ponteiro);
        end loop;
        return 1;
    end;
    ' language plpgsql;
-----------------------------------------
-----------------------------------------
-----------------------------------------
This are the results
teste=# \i for.sql
CREATE TABLE
CREATE TABLE
INSERT 127676 1
INSERT 127677 1
INSERT 127678 1
CREATE FUNCTION
teste=# select copiar();
WARNING:  plpgsql: ERROR during compile of copiar near line 4
ERROR:  missing .. at end of SQL expression
teste=#

Roberto Rezende de Assis <rezende_assis@yahoo.com.br> writes:
> Hello all, why this does not work ?

> create function copiar()
>     returns int as '
>     declare
>         ponteiro int;
>     begin
>         for ponteiro in select * from original order by num asc loop
>             insert into copia(num) values(ponteiro);

The loop variable of a for/select loop has to be a record or rowtype
variable.  So you should do something like

    declare
        r record;
    begin
        for r in select * from original order by num asc loop
            insert into copia(num) values(r.num);

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

I agree that this error message is not very helpful :-(

            regards, tom lane

declarer record;
beginfor r in select * from original order by num asc loop	insert into copia(num) values(r.num);end loop;
end;

I wrote:
> The loop variable of a for/select loop has to be a record or rowtype
> variable.  So you should do something like

>     declare
>         r record;
>     begin
>         for r in select * from original order by num asc loop
>             insert into copia(num) values(r.num);

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

> I agree that this error message is not very helpful :-(

FYI, I have just committed some fixes that will hopefully provide more
helpful error messages for erroneous FOR-loops.  Your example will
draw

ERROR:  loop variable of loop over rows must be a record or row variable

in PG 7.5.

            regards, tom lane

В Сбт, 03.07.2004, в 23:56, Tom Lane пишет:
> Roberto Rezende de Assis <rezende_assis@yahoo.com.br> writes:
> > Hello all, why this does not work ?
>
> > create function copiar()
> >     returns int as '
> >     declare
> >         ponteiro int;
> >     begin
> >         for ponteiro in select * from original order by num asc loop
> >             insert into copia(num) values(ponteiro);
>
> The loop variable of a for/select loop has to be a record or rowtype
> variable.  So you should do something like
>
>     declare
>         r record;
>     begin
>         for r in select * from original order by num asc loop
>             insert into copia(num) values(r.num);
>
> > WARNING:  plpgsql: ERROR during compile of copiar near line 4
> > ERROR:  missing .. at end of SQL expression
>
> I agree that this error message is not very helpful :-(

FWIW, the documentation explicitly states this case and the unhelpful
error message it yields.

I haven't looked, but the docs would need fixing then, too.

Thanks.

--
Markus Bertheau <twanger@bluetwanger.de>

>Hello all, why this does not work ?
>
>-----------------------------------------
>create table original(num int);
>-----------------------------------------
>create table copia(num int);
>-----------------------------------------
>insert into original(num) values (1);
>insert into original(num) values (2);
>insert into original(num) values (3);
>-----------------------------------------
>create function copiar()
>    returns int as '
>    declare
>        ponteiro int;
>    begin
>        for ponteiro in select * from original order by num asc loop
>            insert into copia(num) values(ponteiro);
>        end loop;
>        return 1;
>    end;
>    ' language plpgsql;
>-----------------------------------------
>-----------------------------------------
>-----------------------------------------

Try this...

create function copiar()
    returns int as '
    declare
        ponteiro record;
    begin
        for ponteiro in select * from original order by num asc loop
            insert into copia(num) values(ponteiro.num);
        end loop;
        return 1;
    end;
    ' language plpgsql;

cheers
Jason