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=#



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Thank you all, that one here is the one that worked

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

Roberto Rezende de Assis <rezende_assis (AT) yahoo (DOT) com.br> writes:
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);

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

I wrote:
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

В Сбт, 03.07.2004, в 23:56, Tom Lane пишет:
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 (AT) bluetwanger (DOT) de>


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)