如何将记录传递给 PL/pgSQL 函数? [英] How to pass a record to a PL/pgSQL function?
问题描述
我有8个类似的PL/pgSQL函数;它们用作视图上的 INSTEAD OF INSERT/UPDATE/DELETE
触发器以使其可写.每个视图都组合了一个通用表(在下面的示例中称为事物")和一个特殊表(下面的shape_things"和flavored_things")的列.顺便说一下,PostgreSQL 的继承特性不能用在我们的例子中.
I have 8 similar PL/pgSQL functions; they are used as INSTEAD OF INSERT/UPDATE/DELETE
triggers on views to make them writable. The views each combine columns of one generic table (called "things" in the example below) and one special table ("shaped_things" and "flavored_things" below). PostgreSQL's inheritance feature can't be used in our case, by the way.
触发器必须在通用表中插入/更新行;这些部分在所有 8 个功能中都是相同的.由于通用表有大约 30 列,我试图在那里使用辅助函数,但是我无法将视图的 NEW
记录传递给需要 things的函数代码>记录为输入.
The triggers have to insert/update rows in the generic table; these parts are identical across all 8 functions. Since the generic table has ~30 columns, I'm trying to use a helper function there, but I'm having trouble passing the view's NEW
record to a function that needs a things
record as input.
(在这里和这里,但我没有我想我可以在我的情况下应用建议的解决方案.)
(Similar questions have been asked here and here, but I don't think I can apply the suggested solutions in my case.)
CREATE TABLE things (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
-- (plus 30 more columns)
);
CREATE TABLE flavored_things (
thing_id INT PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
flavor TEXT NOT NULL
);
CREATE TABLE shaped_things (
thing_id INT PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
shape TEXT NOT NULL
);
-- etc...
flavored_things 的可写视图实现
CREATE VIEW flavored_view AS
SELECT t.*,
f.*
FROM things t
JOIN flavored_things f ON f.thing_id = t.id;
CREATE FUNCTION flavored_trig () RETURNS TRIGGER AS $fun$
DECLARE
inserted_id INT;
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO things VALUES ( -- (A)
DEFAULT,
NEW.name
-- (plus 30 more columns)
) RETURNING id INTO inserted_id;
INSERT INTO flavored_things VALUES (
inserted_id,
NEW.flavor
);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE things SET -- (B)
name = NEW.name
-- (plus 30 more columns)
WHERE id = OLD.id;
UPDATE flavored_things SET
flavor = NEW.flavor
WHERE thing_id = OLD.id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
DELETE FROM flavored_things WHERE thing_id = OLD.id;
DELETE FROM things WHERE id = OLD.id;
RETURN OLD;
END IF;
END;
$fun$ LANGUAGE plpgsql;
CREATE TRIGGER write_flavored
INSTEAD OF INSERT OR UPDATE OR DELETE ON flavored_view
FOR EACH ROW EXECUTE PROCEDURE flavored_trig();
上面标记为(A)"和(B)"的语句是我想用对辅助函数的调用来替换的.
The statements marked "(A)" and "(B)" above are what I would like to replace with a call to a helper function.
我最初的尝试是用
inserted_id = insert_thing(NEW);
使用这个功能
CREATE FUNCTION insert_thing (new_thing RECORD) RETURNS INTEGER AS $fun$
DECLARE
inserted_id INT;
BEGIN
INSERT INTO things (name) VALUES (
new_thing.name
-- (plus 30 more columns)
) RETURNING id INTO inserted_id;
RETURN inserted_id;
END;
$fun$ LANGUAGE plpgsql;
失败并显示错误消息PL/pgSQL 函数不能接受类型记录".
当函数被调用为 insert_thing(NEW)
时,给参数类型 things
不起作用:"function insert_thing(flavored_view) 不存在.
Giving the parameter the type things
doesn't work when the function is called as insert_thing(NEW)
: "function insert_thing(flavored_view) does not exist".
这里似乎没有简单的转换;insert_thing(NEW::things)
产生无法将类型 flavored_view 转换为事物".为每个视图编写 CAST 函数将消除我们使用辅助函数获得的结果.
Simple casting doesn't seem to be available here; insert_thing(NEW::things)
produces "cannot cast type flavored_view to things". Writing a CAST function for each view would remove what we gained by using a helper function.
有什么想法吗?
推荐答案
有各种选项,取决于完整的图片.
基本上,您的插入功能可以这样工作:
There are various options, depending on the complete picture.
Basically, your insert function could work like this:
CREATE FUNCTION insert_thing (_thing flavored_view)
RETURNS int AS
$func$
INSERT INTO things (name) VALUES ($1.name) -- plus 30 more columns
RETURNING id;
$func$ LANGUAGE sql;
使用视图的行类型,因为触发器中的NEW
就是这种类型.
使用一个简单的 SQL 函数,它可以被内联并且性能可能更好.
Using the row type of the view, because NEW
in your trigger is of this type.
Use a simple SQL function, which can be inlined and might perform better.
演示电话:
SELECT insert_thing('(1, foo, 1, bar)');
在你的触发器中flavored_trig()
:
inserted_id := insert_thing(NEW);
或者,基本上重写:
IF TG_OP = 'INSERT' THEN
INSERT INTO flavored_things(thing_id, flavor)
VALUES (insert_thing(NEW), NEW.flavor);
RETURN NEW;
ELSIF ...
不是 PL/pgSQL 之外的有效类型,它只是 PL/pgSQL 中未知行类型的通用占位符),因此您不能将其用于函数声明中的输入参数.record
is not a valid type outside PL/pgSQL, it's just a generic placeholder for a yet unknown row type in PL/pgSQL) so you cannot use it for an input parameter in a function declaration.record
对于接受各种行类型的更动态的函数,您可以使用多态类型.示例:
For a more dynamic function accepting various row types you could use a polymorphic type. Examples:
这篇关于如何将记录传递给 PL/pgSQL 函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!