By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,664 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,664 IT Pros & Developers. It's quick & easy.

error: insert has more expressions than target column

P: n/a
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,teache r,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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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,teache r,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

Nov 23 '05 #2

P: n/a
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.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,teache r,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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3

P: n/a
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

P: n/a
Dino Vliet <di********@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

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

Nov 23 '05 #5

P: n/a
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 <de*@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

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

Nov 23 '05 #6

P: n/a
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
Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.