INSERT expecting integer, provided by a TRIGGER...gets error

Question

Using postgreSQL 9.1.9, I have some SQL requests like this:

INSERT INTO "my_table" VALUES(10,'James','California');

Below a simplified table of the original names lookup table...

 names

 name_id      name
 ---          -----
 3            James

but in fact I don't have to enter (into "my_table") the text value provided by the SQL request (name of person, name of the state) but its corresponding ID located in another table (ex: names table)

Thanks to a previous post (execute sql inside plpgsql function) I learned how to create a function that returns the ID based on the name so that I can apply an INSERT with the name:

INSERT INTO my_table(my_table_id, name_id, state) VALUES (10, f_name_id('James'), 'California');

but I would prefer to execute it as a TRIGGER, so I just have to execute a simpler SQL:

INSERT INTO "my_table" VALUES(5,'James','test');

I created all the code below, but when I call the INSERT I get the error

invalid input syntax for integer

"James" (first INSERT command is expecting an integer, but we are providing text. The change text to integer is done later, by the f_id function in fill_table3 TRIGGER)

Some workaround? thanks in advance

        --function to get the ID based on a name (lookup table)
        CREATE OR REPLACE FUNCTION f_id(_table_name text,_name text, OUT _id int) AS
        $func$
        BEGIN
        --EXECUTE 'select id FROM $1 WHERE sector = $2'
        EXECUTE 'select id FROM '||_table_name||' WHERE sector = $2'
           INTO _id
           USING _table_name, _name;
        END
        $func$ LANGUAGE plpgsql;


        --TRIGGER should do the INSERT operation
        CREATE OR REPLACE FUNCTION fill_table3() RETURNS trigger AS
        $func$
        BEGIN

        execute 'insert into '||TG_TABLE_NAME||' values ($1,f_id('||my_table||',$2),$3)';

        END
        $func$ LANGUAGE plpgsql;

        CREATE TRIGGER fill_data
          BEFORE INSERT 
          ON my_table
          FOR EACH ROW
          EXECUTE PROCEDURE fill_table3();

pozs · Accepted Answer · 2014-05-26T12:11:36.870

First, I think you need to understand, how INSERT statement works:

Ommitted columns' default value is calculated (f.ex. next sequence values)
If the expression for any column is not of the correct data type, automatic type conversion will be attempted
BEFORE INSERT trigger(s) fired (if there are any)
After these, PostgreSQL will do the actual insertion OR fire the view's INSTEAD OF trigger(s)/rule.
AFTER INSERT trigger(s) fired (if there are any)

What you want to achieve will never work, because of PostgreSQL will always fail to cast a name (text) to a name_id (integer) at 2. (or will do horrible things, if it can cast it).

However (in theory) you could create an explicit type for this purpose (and create an implicit cast to integer for it), you would just get a fancy function-call, like:

INSERT INTO "my_table" VALUES(5, 'James'::name_to_name_id, 'test');

The closest thing to your goal is, to create an updatable view, which can be managed the way you want it. But personally, I think in this situation, that would be an overkill: simply use your function upon insertion (or use sub-queries instead).

You can't write a cast for a specific domain; you'd need to override `text`->`integer`, which seems a little heavyhanded. Updatable views are the right way to get this working invisibly. — Nick Barnes, May 26 '14 at 11:47

score 0 · Answer 2 · answered May 26 '14 at 10:52

You can use subqueries within INSERT's VALUES list.

INSERT INTO "my_table" (my_table_id, name_id, state)
VALUES
(5, (SELECT id FROM thelookuptable WHERE name = 'James'), 'test');

For more complex cases you can insert over a join that processes the original VALUES clause. This can be really handy when you have a list of values, as shown below:

WITH rawvalues(my_table_id, "name", state) AS (VALUES 
    (10,'James','California'),
    (11,'Bob', 'Western Australia')
)
INSERT INTO my_table
SELECT v.my_table_id, n.name_id, v.state
FROM rawvalues v
INNER JOIN thenamelookuptable n ON (v."name" = n."name");

BTW: I suggest looking into sequences and the SERIAL pseudo-type. Also, you should always specify a column-list in `

INSERT expecting integer, provided by a TRIGGER...gets error

2 Answers2