SELECT in trigger function in two tables

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_h

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

StackExchange Database Administrators Q#106897, answer score: 9