Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unhappy Unanswered: Problems Function/trigger

    I dont know what is the problem with this it appears this error:

    ERROR: SELECT query has no destination for result data

    CREATE FUNCTION gral_desc_count_trg_up() RETURNS OPAQUE AS '

    BEGIN
    SELECT services.gral_desc, services.file_name FROM services WHERE service_id=new.service_id;
    IF FOUND THEN

    UPDATE services SET c_gral_desc=1 WHERE service_id=new.service_id;

    ELSE

    c_gral_desc=0;
    END IF;
    RETURN new;

    END;

    ' LANGUAGE 'plpgsql' VOLATILE;

    CREATE TRIGGER c_gral_desc_trg_up BEFORE UPDATE ON services
    FOR EACH ROW EXECUTE PROCEDURE gral_desc_count_trg_up();



    Please Help!!!

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    what exactly do you want to do with the trigger?

    i use triggers when an update happens on one table and i want to store the old value or a few old values to a log table.

    I am hardly an expert on plpgsql (still trying to figure out what can and cannot be done with it) but you have to declare every variable you are going to use first.

    It would help if you would explain what the trigger is suppose to do and what tables are involved.

    -Ed

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Function/trigger

    I want to update a colum that is c_gral_desc, if u modify the colum gral_desc and file_name c_gral_desc will have a 1, is only in one table
    tablename: services

  4. #4
    Join Date
    Mar 2004
    Posts
    110
    Its more usefull if you put the syntax here how you created the table..

    This is what i've come up with:

    CREATE TABLE services
    (
    gral_desc int,
    file_name varchar(20),
    c_gral_desc int)

    drop function function_update() cascade
    CREATE FUNCTION function_update() RETURNS OPAQUE AS '
    BEGIN
    INSERT INTO services
    VALUES
    (
    NEW.gral_desc,
    NEW.file_name,
    1
    );
    RETURN NULL;
    END;
    ' LANGUAGE 'plpgsql';



    CREATE TRIGGER trigger_update
    AFTER UPDATE
    ON services
    FOR EACH ROW
    EXECUTE PROCEDURE function_update()

    test insert and update:
    insert into services (gral_desc,file_name) values (1,2)
    update services SET gral_desc=2 where file_name=2

    Result:

    facturen=# select * from services;
    gral_desc | file_name | c_gral_desc
    -----------+-----------+-------------
    1 | 2 |
    (1 row)

    facturen=# update services SET gral_desc=2 where file_name=2;
    UPDATE 1
    facturen=# select * from services;
    gral_desc | file_name | c_gral_desc
    -----------+-----------+-------------
    2 | 2 |
    2 | 2 | 1
    (2 rows)

    Hope this helps a bit.

    Goodluck.

    -Ed

Posting Permissions

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