execute sql inside plpgsql function

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)

So i was thinking on creating a trigger, calling a function that should execute the select SQL request and return the modified row to be inserted.

Is that possible?

CREATE FUNCTION insert_by_ID() RETURNS TRIGGER AS '
 BEGIN
 --Can we execute this query, store the result in a variable to be added
 to the NEW record?
 SELECT name_id from names where name=NEW.name;
 --this request would be...
 --SELECT name_id from names where name='James' 
 RETURN NEW;
 END;
 ' LANGUAGE plpgsql;
CREATE TRIGGER insert_info_ID
 BEFORE INSERT ON my_table
 FOR EACH ROW
 EXECUTE PROCEDURE insert_by_ID();

• klin: It is possible, of course, but may be redundant. Complete your question with my_table and names definitions.
• Erwin Brandstetter: Run \d names in psql and post the output. Plus, as always your version of Postgres. And please clarify the fuzzy explanation.
• klin: Your question is still unclear. I can guess you probably need an updateable view. Please read this article: vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger
• Erwin Brandstetter: Are we dealing with names that can always be found in table names or do you want to insert a name if not found and proceed with the newly created name_id? If so, are we dealing with heavy concurrent write load?

Answer 1

Seems you want a solution for the "SELECT or INSERT if not exists" problem.

I suggest a PL/pgSQL function like this:

CREATE OR REPLACE FUNCTION f_name_id(_name text, OUT _name_id int)
 LANGUAGE plpgsql AS
$func$
BEGIN
 LOOP
 BEGIN
 SELECT name_id FROM names WHERE name = _name FOR SHARE -- lock!
 INTO _name_id;
 IF NOT FOUND THEN
 INSERT INTO names (name) VALUES (_name) RETURNING name_id
 INTO _name_id;
 END IF;
 EXCEPTION WHEN UNIQUE_VIOLATION THEN -- inserted concurrently!
 RAISE NOTICE 'It actually happened!'; -- hardly ever happens
 END;
 EXIT WHEN _name_id IS NOT NULL; -- else try again
 END LOOP;
END
$func$;

This is a simple version of the function discussed here in more detail:

• Is SELECT or INSERT in a function prone to race conditions?

Then your INSERT statement can look like this:

INSERT INTO my_table(my_table_id, name_id, state) -- always with column list
VALUES (10, f_name_id('James'), 'California');