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: &quot;SELECT measurement_id FROM measurements ORDER BY measurement_id desc limit 1;&quot;
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?
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;