Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Trigger with query

    Hi to all, it's my first post here!
    I'm new to Pg (migrating from MySQL ) and I'm trying to create a trigger with a select, but I have an error "pq_driver: [PGRES_FATAL_ERROR]ERROR: missing FROM-clause entry for table "val" at character 8
    (caused by statement 'INSERT INTO "edil"."prev_articoli" ( "costo_tot","costo_un","descr","id_articoli","id_p rev_prodotti","quant") VALUES ( '130','65','Demolizione malte vecchie compreso i ponteggi','DEMORE ','3','2')')".

    This trigger would insert a sequence in a table for the record which have the same id_prev_prodotti.

    This is the table prev_articoli:
    CREATE TABLE edil.prev_articoli
    (
    id_prev_prodotti integer,
    id_articoli character(15),
    "H" numeric,
    "L" numeric,
    "P" numeric,
    "SV" numeric,
    "D" numeric,
    costo_un numeric,
    costo_tot numeric,
    quant numeric,
    descr text,
    pos integer,
    idprev_articoli serial NOT NULL,
    CONSTRAINT idprev_articoli PRIMARY KEY (idprev_articoli),
    CONSTRAINT fk_prev_art_art FOREIGN KEY (id_articoli)
    REFERENCES edil.articoli (code) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT fk_prev_prev_prod FOREIGN KEY (id_prev_prodotti)
    REFERENCES edil.prev_prodotti (idprev_prodotti) MATCH FULL
    ON UPDATE NO ACTION ON DELETE NO ACTION
    )

    This is the trigger:
    CREATE TYPE risultato AS (posizione INTEGER);
    CREATE OR REPLACE FUNCTION edil.incr_pos()
    RETURNS trigger AS
    $BODY$
    DECLARE
    val risultato;
    id integer;
    BEGIN
    id = NEW.id_prev_prodotti;
    SELECT MAX(prev_articoli.pos) INTO val FROM edil.prev_articoli AS prev_articoli WHERE prev_articoli.id_prev_prodotti = id GROUP BY prev_articoli.pos LIMIT 1;
    NEW.pos := val.pos;
    RETURN NEW;
    END;$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION edil.incr_pos() OWNER TO edil;

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    field name "id_p rev_prodotti" in your insert statement doesn't match the field name inthe dable definition.

    Also, numeric values do not need to be delimited with the apostrophe ( ' ) character.

    And, if the column names are defined as lower case and do not have spaces or other unallowed characters, they do not need to be quoted.

    It also appears that you have an extra close paren ')' at the end.
    So,
    Code:
    INSERT INTO "edil"."prev_articoli" ( "costo_tot","costo_un","descr","id_articoli","id_p rev_prodotti","quant") VALUES ( '130','65','Demolizione malte vecchie compreso i ponteggi','DEMORE ','3','2')')
    would become
    Code:
    INSERT INTO edil.prev_articoli ( costo_tot, costo_un, descr, id_articoli, id_p_rev_prodotti, quant) VALUES ( 130,65,'Demolizione malte vecchie compreso i ponteggi','DEMORE ',3,2)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2010
    Posts
    4
    Thank for the reply, but the error isn't in the INSERT statement (it works fine disabling the trigger), but in the trigger (the space is an error of paste, the extra ')' is inserted by Postgre, and the ' surrounding the number don't cause error, it seems, but if unuseful I will delete them).

  4. #4
    Join Date
    Nov 2010
    Posts
    4

    Smile

    I do some work, and now it's working like this:

    CREATE OR REPLACE FUNCTION edil.incr_pos()
    RETURNS trigger AS
    $$
    DECLARE
    v_pos RECORD;
    rec integer;
    BEGIN
    SELECT pos INTO v_pos FROM edil.prev_articoli
    WHERE id_prev_prodotti = NEW.id_prev_prodotti LIMIT 1;
    rec := v_pos.pos+1;
    IF rec IS NULL THEN
    rec = 1;
    END IF;
    NEW.pos := rec;
    RETURN NEW;
    END
    $$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION edil.incr_pos() OWNER TO edil;

    I can't use the MAX function, but perhaps is only some difference in PostgreSQL, I have to read some docs

  5. #5
    Join Date
    Nov 2010
    Posts
    4
    This is the final trigger, I solved with an ORDER BY ... DESC LIMIT 1
    CREATE OR REPLACE FUNCTION edil.incr_pos()
    RETURNS trigger AS
    $BODY$
    DECLARE
    v_pos RECORD;
    rec integer;
    BEGIN
    SELECT pos INTO v_pos FROM edil.prev_articoli
    WHERE id_prev_prodotti = NEW.id_prev_prodotti
    ORDER BY pos DESC LIMIT 1;
    rec := v_pos.pos+1;
    IF rec IS NULL THEN
    rec = 1;
    END IF;
    NEW.pos := rec;
    RETURN NEW;
    END
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;

Posting Permissions

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