4

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:

CREATE TABLE measurements (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL
    );    

CREATE TABLE file_headers (
        header_id SERIAL PRIMARY KEY NOT NULL, 
        measurement_id INTEGER NOT NULL, 
        file_header_index_start INTEGER,
        file_header_index_end INTEGER
    );

CREATE TRIGGER measurement_ids AFTER INSERT 
        ON measurements FOR EACH ROW 
        EXECUTE PROCEDURE ins_function('SELECT measurement_id FROM measurements 
        ORDER BY measurement_id desc limit 1;', 1, 666 ); 

where I assumed that the datatype of the SELECT is INTEGER since SERIAL but it is apparently false because I get the error message from this command which starts the trigger:

INSERT INTO measurements (measurement_size_in_bytes) VALUES (888);` 
ERROR:  invalid input syntax for integer: "SELECT measurement_id FROM measurements ORDER BY measurement_id desc limit 1;"
CONTEXT:  PL/pgSQL function ins_function() line 10 at assignment

Edit

ins_function() and edits based on @a_horse_with_no_name and @Joishi's comments:

CREATE OR REPLACE FUNCTION ins_function() RETURNS TRIGGER AS $$
    --
    -- Perform AFTER INSERT operation on file_header by creating rows with new.measurement_id, new.file_header_index_start and new.file_header_index_end.
    --
DECLARE
    measurement_id              INTEGER;
    file_header_index_start     INTEGER;
    file_header_index_end       INTEGER; 
BEGIN     

    SELECT a.measurement_id INTO measurement_id from measurements a ORDER BY measurement_id desc limit 1;
    file_header_index_start := TG_ARGV[0];
    file_header_index_end := TG_ARGV[1]; 

    IF TG_OP = 'INSERT' THEN
        INSERT INTO file_headers (measurement_id, file_header_index_start, file_header_index_end)
        VALUES (measurement_id, file_header_index_start, file_header_index_end); 
        RETURN NEW; 
    END IF;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

--
-- Function and trigger on INSERT. 
--
CREATE TRIGGER measurement_ids AFTER INSERT 
    ON measurements FOR EACH ROW EXECUTE PROCEDURE ins_function(1, 666); 

I get now no error but the output is wrong: no INSERT seen in the table file_headers while successfully in the table measurements.

Output of @ErwinBrandstetter's answer

So I started to think about casting from TEXT to INT but this should be so basic operation, since TG_ARGV[] is a datatype of text. One unsuccessful try is format('SELECT $1.%I', TG_ARGV[0]). The regclass could work as you describe here in insaft_function()

SELECT NEW.measurement_id, TG_ARGV[0]::regclass, TG_ARGV[1]::regclass;

Why are there no successful INSERTs into the table file_headers?

  • 1
    Show us the definition of the ins_function() – a_horse_with_no_name Jul 14 '15 at 14:36
  • 1
    You pass in a text (actually unknown) to the trigger, it does not match the type defined for measurement_id in ins_function(). What do you want to achieve? – dezso Jul 14 '15 at 14:51
  • 2
    You are passing a string constant SELECT ... to the trigger function, not the result of a SELECT statement. You need to move that select into the trigger function, you can't dynamically pass parameters like that. – a_horse_with_no_name Jul 14 '15 at 15:04
  • 1
    You have a measurement_id defined within your function, and there is also a measurement_id defined within the table you are querying ... thus it is confused as to which one you are wanting it to grab (thus ambiguous). You need to alias your table.. SELECT a.measurement_id INTO measurement_id FROM measurements a ... – Joishi Bodio Jul 14 '15 at 15:20
  • 1
    Do you have a 2nd, different, trigger on the file_headers table that may be changing the value to 100 (instead of 666)? – Joishi Bodio Jul 14 '15 at 15:43
5

You have an unresolved naming conflict.

You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting.

Here you declare a variable named measurement_id:

DECLARE
    measurement_id              INTEGER;

It's a folly to use ambiguous variable names to begin with. If you do it anyway, you must know what you are doing. I make it a habbit to prepend variable names with an underscore unlike column names, like _measurement_id.

The later SELECT statement is ambiguous:

ORDER BY measurement_id

This would raise an error message in modern PostgreSQL with default configuration. Per the documentation:

By default, PL/pgSQL will report an error if a name in a SQL statement could refer to either a variable or a table column.

And:

To change this behavior on a system-wide basis, set the configuration parameter plpgsql.variable_conflict to one of error, use_variable, or use_column (where error is the factory default). This parameter affects subsequent compilations of statements in PL/pgSQL functions, but not statements already compiled in the current session. Because changing this setting can cause unexpected changes in the behavior of PL/pgSQL functions, it can only be changed by a superuser.

In Postgres older than 9.0 this would be resolved to mean the variable. Per the documentation

In such cases you can specify that PL/pgSQL should resolve ambiguous references as the variable (which is compatible with PL/pgSQL's behavior before PostgreSQL 9.0)

Bold emphasis mine.
This would result in arbitrary results, since the sort order is now undetermined.

Audited Function

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
DECLARE
   _measurement_id          integer;
   _file_header_index_start integer := TG_ARGV[0]::int;
   _file_header_index_end   integer := TG_ARGV[1]::int; 
BEGIN     

   SELECT a.measurement_id   INTO _measurement_id
   FROM   measurements a
   ORDER  BY a.measurement_id DESC  -- you had ambiguity here!
   LIMIT  1;

   IF TG_OP = 'INSERT' THEN  -- noise if only used in AFTER INSERT trigger
      INSERT INTO file_headers (measurement_id, file_header_index_start
                                              , file_header_index_end)
      VALUES (_measurement_id, _file_header_index_start, _file_header_index_end); 
   END IF;

   RETURN NULL; -- result is ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

Note how I named it insaft_function(), since this is only to be used in an AFTER INSERT trigger.

Trigger:

CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function(1, 666);

But for the provided setup, you can radically simplify the function:

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
BEGIN     
   INSERT INTO file_headers (measurement_id, file_header_index_start
                                           , file_header_index_end)
   VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);

   RETURN NULL;  -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;
  • 1
    @Masi: I added the missing cast to integer and the semicolon. Tested in pg 9.4 and works for me. Casting to regclass would not make sense. Here is an example with proper use of regclass: stackoverflow.com/a/10711349/939860 – Erwin Brandstetter Jul 15 '15 at 13:26
  • 1
    @Masi: VALUES or SELECT, either method works, I tested both. I just wanted to stay consistent with the first version, no other reason. The error you get is due to missing parentheses. VALUES requires parentheses. – Erwin Brandstetter Jul 15 '15 at 14:04
  • 1
    @Masi: No measurable difference. VALUES can provide multiple rows from given expressions, but not much more, SELECT has a whole arsenal of techniques at its fingertips. Basic expressions in the list are evaluated the same. – Erwin Brandstetter Jul 15 '15 at 14:11
  • 1
    Start by reading the manual on VALUES here. If you have a new question, start a new question with all the necessary details. Comments are not the place. – Erwin Brandstetter Jul 15 '15 at 14:21
  • I extended the evaluation here dba.stackexchange.com/q/107055/69807 – Léo Léopold Hertz 준영 Jul 15 '15 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.