将记录作为函数参数PL/pgSQL传递 [英] Passing a record as function argument PL/pgSQL

问题描述

首先，我真的是pl/pgsql的新手.需要一个项目.

First I am really new to pl/pgsql. Need it for a project.

我被这个(简化的)问题困住了.

I am stuck with this (simplified) problem.

我的数据库架构具有n对m的关系(作者，书籍，author_books)

My db schema has a n to m relationship (author, books, author_books)

现在，我想拥有一个pl/psgsql函数insert_book. (我确实知道所有作者都已经在作者表中，因此我只想传递其主键.)

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).

此功能概述是我的构想.

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;

作为论据，我想传递一个book类型和写它的作者的记录.但是，这将如何工作呢?我在Google上搜索了很多，似乎无法弄清楚……

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...

问题1 :功能概述是否正确/是否合理?

Question 1: Is the function outline "correct"/does it make sense?

问题2 :如何将记录簿插入到桌面簿中?我是否必须遍历书籍的所有字段(书名，isbn，出版商...)，然后将它们添加到INSERT INTO语句中?还是有更智能"的方式?

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?

问题3 :如何调用我的函数insert_book?我在这里找到了这个示例(http://dbaspot.com/postgresql/206142-passing-record-function-argument-pl-pgsql.html)，但这并不能真正帮助我.出于测试目的，我使用外壳程序，但稍后我们将Java与JDBC一起使用.

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.

非常感谢您的帮助.

推荐答案

使用修改数据的CTE (需要Postgres 9.1或更高版本)，这可以是一个简单的SQL查询:

Using unnest() and a data-modifying CTE (requires Postgres 9.1 or later), this can be a simple SQL query:

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

第一个CTE x仅用于简化数据输入，并非严格要求.

The first CTE x is just for simplified data input and not strictly needed.

问题1:功能概述是否正确/有意义吗?

Question 1: Is the function outline "correct"/does it make sense?

传递基本类型而不是复合类型book可能更容易，但这是一种完全有效的方法.但是，您必须了解复杂类型的语法.例如，在我的示例中，请注意名称旁边的括号:(x._book).*.

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).*.

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;

问题2:如何将记录簿插入到桌面簿中? (...)还是有更智能"的方式?

Question 2: How to insert record book into table book? (...) or is there a "smarter" way?

更聪明的方法是用(variable_name).*分解复合类型.

The smarter way is to decompose the composite type with (variable_name).*.

由于 type 保证与table匹配(从中衍生)，因此这是极少数情况下的一种情况，完全可以，不是在持久代码中为INSERT命令提供列列表.

As the type is guaranteed to match the table (being derived from it), this is one of the rare cases, where it is perfectly ok, not to provide a column list for the INSERT command in persisted code.

问题3:如何调用函数insert_book? ...

Question 3: How would I call my function insert_book? ...

SELECT f_insert_book('(1,foo_book)'::book, '{1,2,3}'::int[]);

在其他plpgsql函数中，使用 PERFORM INTO foo)，则使用a>而不是SELECT.

Within other plpgsql functions, use PERFORM instead of SELECT if you don't provide a target (INTO foo) for the (non-existing) results.

这篇关于将记录作为函数参数PL/pgSQL传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！