SELECT in trigger function in two tables

Question

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?

• user1822: Show us the definition of the ins_function()
• András Váczi: 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?
• user1822: 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.
• Joishi Bodio: 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: Do you have a 2nd, different, trigger on the file_headers table that may be changing the value to 100 (instead of 666)?

Answer 1

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 habit 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. The manual:

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. The manual:

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
 LANGUAGE plpgsql 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$;

Note the name insaft_function(): 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
 LANGUAGE plpgsql 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$;

• Erwin Brandstetter: @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: @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: @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: 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.
• Léo Léopold Hertz 준영: I extended the evaluation here dba.stackexchange.com/q/107055/69807