Thread: error: insert has more expressions than target column

error: insert has more expressions than target column

From
Dino Vliet
Date:
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

Re: error: insert has more expressions than target column

From
Richard Huxton
Date:
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

Re: error: insert has more expressions than target column

From
Dino Vliet
Date:
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

Re: error: insert has more expressions than target column

From
Richard Huxton
Date:
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

Re: error: insert has more expressions than target column

From
Tom Lane
Date:
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

Re: error: insert has more expressions than target column

From
Dino Vliet
Date:
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

Re: error: insert has more expressions than target column

From
Björn Lundin
Date:
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