Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2008
    Posts
    1

    Unanswered: Update Table when trigger fired & called a function

    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.

  2. #2
    Join Date
    Aug 2008
    Posts
    1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •