error: insert has more expressions than target column

[Dino Vliet]

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,start date,endate,sta rtime,endtime,t eacher,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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

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,start date,endate,sta rtime,endtime,t eacher,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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Dino Vliet]

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 <de*@archonet.c om> 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,start date,endate,sta rtime,endtime,t eacher,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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Dino Vliet <di********@yah oo.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Dino Vliet]

MUCH better now....I did manage to get an insert into
the table lessons with these adjustments...B UT 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,start date + (i*7), enddate +
(i*7),...;
RETURN NEW;
END LOOP;
END;
' LANGUAGE plpgsql;
--- Richard Huxton <de*@archonet.c om> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Björn Lundin]

Dino Vliet wrote:

MUCH better now....I did manage to get an insert into
the table lessons with these adjustments...B UT 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,start date + (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