在plpgsql函数中执行sql [英] execute sql inside plpgsql function
问题描述
使用postgreSQL 9.1.9,我有一些这样的SQL请求:
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
,但实际上我不必输入(请输入"my_table")SQL请求提供的文本值(人名,州名),但其相应的ID位于另一个表中(例如:names表) )
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)
所以我在考虑创建一个触发器,调用一个函数,该函数应执行select SQL请求并返回要插入的修改后的行.
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.
有可能吗?
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();
推荐答案
我的有根据的猜测是您想要解决"SELECT或INSERT如果不存在"的问题.
My educated guess is you want a solution for the "SELECT or INSERT if not exists" problem.
我建议您创建一个如下的plpgsql函数:
I suggest you create a plpgsql function like this:
CREATE OR REPLACE FUNCTION f_name_id(_name text, OUT _name_id int) 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$ LANGUAGE plpgsql;
这是的简单版本(更多说明和那里的链接):
是SELECT还是INSERT功能容易出现种族状况?
This is a simple version of (more explanation and links over there):
Is SELECT or INSERT in a function prone to race conditions?
然后您的INSERT
语句如下所示:
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');
这篇关于在plpgsql函数中执行sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!