[Ila]

Hi List,

I am newbie to this forum & also to PostgreSQL. I have created a table like:

CREATE TABLE user_event_reviews
(
user_event_review_id double precision NOT NULL,
userid character varying(50),
title character varying(200),
rating character varying(20),
user_event_id integer,
entrydate timestamp without time zone,
updatedate timestamp without time zone,
CONSTRAINT user_event_reviews_pkey PRIMARY KEY (user_event_review_id)
)
WITH (OIDS=FALSE);
ALTER TABLE user_event_reviews OWNER TO postgres;

Then created a TRIGGER to be fired on INSERT OPERATION as:

CREATE TRIGGER user_event_review_id_trig
BEFORE INSERT
ON user_event_reviews
FOR EACH ROW
EXECUTE PROCEDURE user_event_review_id_trig();

A SEQUENCE as:
CREATE SEQUENCE user_event_review_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 21474836
START 467
CACHE 20;
ALTER TABLE user_event_review_id_seq OWNER TO postgres;

And finally a function(having returntype TRIGGER):

CREATE OR REPLACE FUNCTION user_event_review_id_trig()
RETURNS trigger AS
$BODY$
begin
select nextval('user_event_review_id_seq') into new.user_event_review_id;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 10;
ALTER FUNCTION user_event_review_id_trig() SET search_path=xyz;
ALTER FUNCTION user_event_review_id_trig() OWNER TO postgres;

But while inserting data thrugh INSERT statement as:
INSERT INTO user_event_reviews VALUES (317,'kookie monster', 'All about plastics', 'Its a little different to hear about an exhibition on plastics first', 0, 338, 'now', 'now');

I am getting an ERROR as:
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function "user_event_review_id_trig"

********** Error **********

ERROR: control reached end of trigger procedure without RETURN
SQL state: 2F005
Context: PL/pgSQL function "user_event_review_id_trig"

Please help me out if anyone has any idea about it.I would be very thankful to the responser's. I would really appreciate your thoughts & suggestions.

[karavelov]

First - the quick solution to your problem:

CREATE TABLE user_event_reviews (
user_event_review_id serial PRIMARY KEY,
userid character varying(50),
title character varying(200),
rating character varying(20),
user_event_id integer,
entrydate timestamp without time zone DEFAULT now(),
updatedate timestamp without time zone DEFAULT now()
);
ALTER TABLE user_event_reviews OWNER TO postgres;

That's it. Automatically there will be created sequence for 'user_event_review_id' and index for the primary key.

Now you could insert some data. If you do not supply value for user_event_review_id it will be generated from the sequence. Also if you do not supply the timestamps the will default to current_timestamp;

INSERT INTO user_event_reviews (userid,title,rating,user_event_id)
VALUES ('kookie monster','All about plastics','Its a little different to hear about an exhibition on plastics first', 338);


If you still want to use triggers for the same you have to end the function with return statement. So adding "return new;" at the end will do the job.