Thread: ERROR: syntax error at or near "select" at character 9

Hi

I am using PG 8.0 on Win32, learning SQL.
I dod create a table with the following SQL:
  CREATE TABLE test (      id integer,      nev varchar(25),      datum timestamp  );

A sequence with:
  CREATE SEQUENCE "public"."test_azon_seq"      INCREMENT 1  MINVALUE 101      MAXVALUE 9223372036854775807  START 101
   CACHE 1  ;
 

A before insert trigger:
  CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$      BEGIN          NEW.id := select
nextval('test_azon_seq');         NEW.nev := nev;          NEW.datum := current_timestamp;          RETURN NEW;
END; $test_verif$ LANGUAGE plpgsql  ;
 
  CREATE TRIGGER test_verif BEFORE INSERT ON test      FOR EACH ROW EXECUTE PROCEDURE test_verif()  ;

When I issue the
  insert into "public"."test" (nev) values ('Graf László');

insert statement, it returns

"ERROR:  syntax error at or near "select" at character 9".

Why? What is wrong?

-- 
László Graf

On Fri, 2 Sep 2005, [ISO-8859-2] Graf L�szl� wrote:

>
>    CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
>        BEGIN
>            NEW.id := select nextval('test_azon_seq');

I think you want to remove select here, you're already effectively doing a
select of the right hand side in the assignment.

>            NEW.nev := nev;
I think you want to remove this line entirely.  What nev were you
expecting on the right hand side?  If it's the new one, well, NEW.new is
already that.

In article <20050905213103.F5782@megazone.bigpanda.com>,
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

> On Fri, 2 Sep 2005, [ISO-8859-2] Graf László wrote:

>>
>> CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
>> BEGIN
>> NEW.id := select nextval('test_azon_seq');

> I think you want to remove select here, you're already effectively doing a
> select of the right hand side in the assignment.

>> NEW.nev := nev;
> I think you want to remove this line entirely.  What nev were you
> expecting on the right hand side?  If it's the new one, well, NEW.new is
> already that.

László could also remove the entire trigger and use something like


CREATE TABLE test ( id SERIAL NOT NULL, nev VARCHAR(25), datum TIMESTAMP NOT NULL DEFAULT current_timestamp, PRIMARY
KEY(id) 
);

On Tue, 6 Sep 2005, Harald Fuchs wrote:

> In article <20050905213103.F5782@megazone.bigpanda.com>,
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>
> > On Fri, 2 Sep 2005, [ISO-8859-2] Graf László wrote:
>
> >>
> >> CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
> >> BEGIN
> >> NEW.id := select nextval('test_azon_seq');
>
> > I think you want to remove select here, you're already effectively doing a
> > select of the right hand side in the assignment.
>
> >> NEW.nev := nev;
> > I think you want to remove this line entirely.  What nev were you
> > expecting on the right hand side?  If it's the new one, well, NEW.new is
> > already that.
>
> László could also remove the entire trigger and use something like
>
>
> CREATE TABLE test (
>   id SERIAL NOT NULL,
>   nev VARCHAR(25),
>   datum TIMESTAMP NOT NULL DEFAULT current_timestamp,
>   PRIMARY KEY (id)
> );

That's slightly different though.  The trigger forces the value whether or
not a value was assigned in the insert, the defaults only apply if the
column does not have a value given to it.