Обсуждение: error: insert has more expressions than target column
Hi there, I want to put a number of records (variable number depending on a attribute of a table) into a certain table with a trigger statement. I have created the follwing trigger: CREATE FUNCTION vullalles() RETURNS trigger AS ' BEGIN FOR i in 0..7 LOOP INSERT INTO lessons (select dayofweek,startdate,endate,startime,endtime,teacher,location,roomnr from courseschedule); startdate := startdate + i*7; EXECUTE startdate; RETURN NEW; END LOOP; END; ' LANGUAGE plpgsql; When I insert a record into courseschedule, I get the following error: insert has more expressions than target column WHAT AM I DOING WRONG? The function should insert 8 records into lessons when I insert one record in courseschedule. Now it is done with a hardcoded for loop but eventually it should be done based on a attribute in courseschedule. How can I fix this? I'm using postgresql 7.4.3 under Freebsd. _______________________________ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush
Dino Vliet wrote: > Hi there, > I want to put a number of records (variable number > depending on a attribute of a table) into a certain > table with a trigger statement. > > I have created the follwing trigger: > > CREATE FUNCTION vullalles() RETURNS trigger AS ' > BEGIN > FOR i in 0..7 LOOP > INSERT INTO lessons (select > dayofweek,startdate,endate,startime,endtime,teacher,location,roomnr > from courseschedule); Try it without the brackets around select, or put the column-names in brackets before it. > startdate := startdate + i*7; > EXECUTE startdate; Not sure what this is supposed to be doing. The EXECUTE is redundant. > RETURN NEW; > END LOOP; > END; > ' LANGUAGE plpgsql; -- Richard Huxton Archonet Ltd
I'm getting the same error without brackets. The EXECUTE statement was because I read something about executing dynamic content. I want to add 7 days to the date value of startdate and want to repeat it every week. Because there are 8 weeks I choose to do that with the for loop going from 0 to 7. Thanks in advance --- Richard Huxton <dev@archonet.com> wrote: > Dino Vliet wrote: > > Hi there, > > I want to put a number of records (variable number > > depending on a attribute of a table) into a > certain > > table with a trigger statement. > > > > I have created the follwing trigger: > > > > CREATE FUNCTION vullalles() RETURNS trigger AS ' > > BEGIN > > FOR i in 0..7 LOOP > > INSERT INTO lessons (select > > > dayofweek,startdate,endate,startime,endtime,teacher,location,roomnr > > from courseschedule); > > Try it without the brackets around select, or put > the column-names in > brackets before it. > > > startdate := startdate + i*7; > > EXECUTE startdate; > > Not sure what this is supposed to be doing. The > EXECUTE is redundant. > > > RETURN NEW; > > END LOOP; > > END; > > ' LANGUAGE plpgsql; > > -- > Richard Huxton > Archonet Ltd > __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
Dino Vliet wrote: > I'm getting the same error without brackets. Check the columns in table "lessons" matches the columns in your select. > The EXECUTE statement was because I read something > about executing dynamic content. > > I want to add 7 days to the date value of startdate > and want to repeat it every week. Because there are 8 > weeks I choose to do that with the for loop going from > 0 to 7. Looking closer, I can see the problem. You're treating the column from the select as a variable (which it isn't). Try something like: INSERT INTO lessons (col_name1, col_name2, ...) SELECT dayofweek, startdate + (i*7), endate + (i*7), startime, ... -- Richard Huxton Archonet Ltd
Dino Vliet <dino_vliet@yahoo.com> writes:
> I'm getting the same error without brackets.
The message says you are trying to insert more values than the "lessons"
table has columns.
regards, tom lane
MUCH better now....I did manage to get an insert into the table lessons with these adjustments...BUT now it seems the FOR LOOP didn't work because I only get 1 record and expected that I would get 8 records due to the i variabele. What could be wrong? My code is now: CREATE FUNCTION vulalles() RETURNS trigger AS ' BEGIN FOR i in 0..7 LOOP INSERT INTO lessons (......) SELECT dayofweek,startdate + (i*7), enddate + (i*7),...; RETURN NEW; END LOOP; END; ' LANGUAGE plpgsql; --- Richard Huxton <dev@archonet.com> wrote: > Dino Vliet wrote: > > I'm getting the same error without brackets. > > Check the columns in table "lessons" matches the > columns in your select. > > > The EXECUTE statement was because I read something > > about executing dynamic content. > > > > I want to add 7 days to the date value of > startdate > > and want to repeat it every week. Because there > are 8 > > weeks I choose to do that with the for loop going > from > > 0 to 7. > > Looking closer, I can see the problem. You're > treating the column from > the select as a variable (which it isn't). > > Try something like: > > INSERT INTO lessons (col_name1, col_name2, ...) > SELECT dayofweek, startdate + (i*7), endate + (i*7), > startime, ... > > -- > Richard Huxton > Archonet Ltd > __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
Dino Vliet wrote: > MUCH better now....I did manage to get an insert into > the table lessons with these adjustments...BUT now it > seems the FOR LOOP didn't work because I only get 1 > record and expected that I would get 8 records due to > the i variabele. > > What could be wrong? > > My code is now: > > CREATE FUNCTION vulalles() RETURNS trigger AS ' > BEGIN > FOR i in 0..7 LOOP > INSERT INTO lessons (......) > SELECT dayofweek,startdate + (i*7), enddate + > (i*7),...; > RETURN NEW; > END LOOP; > END; > ' LANGUAGE plpgsql; Is the 'RETURN NEW' statement supposed to be _BEFORE_ end loop? To me, it looks like you are returning from the function in the first loop turn. /Njörn