postgresql 将记录作为函数参数 PL/pgSQL 传递
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12716024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing a record as function argument PL/pgSQL
提问by taranaki
First I am really new to pl/pgsql. Need it for a project.
首先，我对 pl/pgsql 真的很陌生。一个项目需要它。
I am stuck with this (simplified) problem.
我被这个（简化的）问题困住了。
My db schema has a n to m relationship (author, books, author_books)
我的 db 架构有一个 to m 关系（作者、书籍、author_books）
Now I want to have a pl/psgsql function insert_book. (I do know that all authors are definitely already in the author table, so I just want to pass their primary keys).
现在我想要一个 pl/psgsql 函数 insert_book。（我知道所有作者肯定已经在作者表中，所以我只想传递他们的主键）。
This function outline is what I have in mind.
这个功能大纲是我想到的。
create or replace function insert_book(book_to_insert book, authors integer[])
returns void as $$
begin
-- insert book into table books
-- for each author add an entry to author_books table
end;
$$ language plpgsql;
As arguments I thought to pass a record of type book and the authors that wrote it. But how exactly would this work? I googled quite a bit and can't seem to figure this out...
作为论点，我想传递类型 book 和写它的作者的记录。但这究竟是如何运作的呢？我用谷歌搜索了很多，似乎无法弄清楚这一点......
Question 1: Is the function outline "correct"/does it make sense?
问题 1：函数大纲“正确”/有意义吗？
Question 2: How to insert record book into table book? Do I have to go over all fields of book (title, isbn, publisher,...) and add them to an INSERT INTO statement or is there a "smarter" way?
问题2：如何将记录簿插入到表簿中？我是否必须查看书籍的所有领域（标题、书名、出版商等）并将它们添加到 INSERT INTO 语句中，还是有“更智能”的方法？
Question 3: How would I call my function insert_book? I found this example here (http://dbaspot.com/postgresql/206142-passing-record-function-argument-pl-pgsql.html), but that doesn't really help me. For testing purposes I am using the shell, but later on we will use Java with JDBC.
问题 3：如何调用我的函数 insert_book？我在这里找到了这个例子（http://dbaspot.com/postgresql/206142-passing-record-function-argument-pl-pgsql.html），但这并没有真正帮助我。出于测试目的，我使用的是 shell，但稍后我们将使用 Java 和 JDBC。
Thank you very much for your help.
非常感谢您的帮助。
采纳答案by Erwin Brandstetter
Using unnest()and a data-modifying CTE(requires Postgres 9.1 or later), this can be a simple SQL query:
使用unnest()和数据修改 CTE（需要 Postgres 9.1 或更高版本），这可以是一个简单的 SQL 查询：
WITH x AS (SELECT '(1,foo_book)'::book AS _book
, '{1,2,3}'::int[] AS _authors)
, y AS (
INSERT INTO book -- no column list, correct due to composite type
SELECT (x._book).*
FROM x
RETURNING book_id
)
INSERT INTO author_book (book_id, author_id)
SELECT y.book_id, unnest(x._authors)
FROM x,y; -- CROSS JOIN ok, only 1 row for x and y
The first CTE xis just for simplified data input and not strictly needed.
第一个 CTEx只是为了简化数据输入，并不是严格需要的。
SQL Fiddle.
SQL小提琴。
As to your questions:
至于你的问题：
Question 1: Is the function outline "correct"/does it make sense?
问题 1：函数大纲“正确”/有意义吗？
Might be easier to pass base types instead of the composite type book, but it is a perfectly valid approach. You have to know your way around the syntax for complex types, though. For instance, note the parenthesis around the name in my example: (x._book).*.
传递 base types 而不是复合 type 可能更容易book，但它是一种完全有效的方法。不过，您必须了解复杂类型的语法。例如，请注意我的示例中名称周围的括号：(x._book).*.
A plpgsql functioncould look like this:
一个PLPGSQL功能看起来是这样的：
CREATE OR REPLACE FUNCTION f_insert_book(_book book, _authors integer[])
RETURNS void AS
$func$
BEGIN
WITH y AS (
INSERT INTO book b
SELECT (_book).*
RETURNING b.book_id
)
INSERT INTO author_book (book_id, author_id)
SELECT y.book_id, unnest(_authors)
FROM y;
END
$func$ LANGUAGE plpgsql;
Question 2: How to insert record book into table book? (...) or is there a "smarter" way?
问题二：如何将记录簿插入到表簿中？(...) 还是有“更聪明”的方法？
The smarter way is to decompose the composite type with (variable_name).*.
更聪明的方法是使用(variable_name).*.
As the typeis guaranteed to match the table(being derived from it), this is one of the rare cases, where it is perfectly ok, notto provide a column list for the INSERTcommand in persisted code.
由于类型保证匹配table（从它派生），这是极少数情况之一，在这种情况下完全可以，不在INSERT持久化代码中为命令提供列列表。
Question 3: How would I call my function insert_book? ...
问题 3：如何调用我的函数 insert_book？...
SELECT f_insert_book('(1,foo_book)'::book, '{1,2,3}'::int[]);
Within other plpgsql functions, use PERFORMinstead of SELECTif you don't provide a target (INTO foo) for the (non-existing) results.
在其他 plpgsql 函数中，如果您没有为（不存在的）结果提供目标 ( ) ，请使用PERFORM代替。SELECTINTO foo
回答by LordZero
Passing JSON datatype (Postgresql 9.2 or higher):
传递 JSON 数据类型（Postgresql 9.2 或更高版本）：
CREATE OR REPLACE FUNCTION f_insert_book(_book json, _authors json)
RETURNS void AS
$$
BEGIN
-- insert book into table books
Insert into books values select * from json_populate_recordset(null:book, _book);
-- for each author add an entry to author_books table
Insert into authors values select * from json_populate_recordset(null:authors, _authors);
end;
$$ language plpgsql;
