error: insert has more expressions than target column

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