postgresql - 从 PL/pgSQL 函数中的 INSERT INTO 访问并返回结果

Question

我目前正在学习大量 PostgreSQL，尤其是 PLPGSQL，并且正在努力处理函数中的查询结果。我想围绕用户表创建一个包装器，稍后使用结果然后返回它。在我的例子中，用户和帐户是两个不同的表，我想一次性创建它。

我的第一个天真的方法是构建以下内容:

CREATE OR REPLACE FUNCTION schema.create_user_with_login (IN email varchar, IN password varchar, IN firstname varchar DEFAULT NULL, IN surname varchar DEFAULT NULL)
    RETURNS schema.user
    LANGUAGE plpgsql
    VOLATILE 
    RETURNS NULL ON NULL INPUT
    AS 
$$
declare
  created_user schema."user";
begin

  INSERT INTO schema."user" ("firstname", "surname", "email")
    VALUES (firstname, surname, email)
    RETURNING * INTO created_user;

  // [...] create accounts and other data using e.g. created_user.id

  // the query should return the initially created user
  RETURN created_user
end;
$$;

这种方法不起作用，因为 schema.user 有 NOT NULL 字段(具有该约束的域类型)并且会为声明的语句抛出异常:

domain schema."USER_ID" does not allow null values

所以也许它可以工作，但在那个受限的环境中不行。

我还尝试使用 RETURNS SETOF schema.user 并直接使用 RETURN QUERY INSERT ....，但这不会返回所有列，而是返回一列所有数据。

我怎样才能实现将初始用户对象作为适当的用户行返回的效果，同时在函数内部提供数据？

我正在使用 Postgres 9.6。我的版本输出:

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

Answer 1

问题 1

I also tried to use RETURNS SETOF schema.user and directly RETURN QUERY INSERT ...., but this does not return all columns, but instead one column with all the data.

肯定会返回所有列。您必须像这样调用设置返回函数:

<b>SELECT * FROM</b> schema.create_user_with_login;

您必须将其声明为 RETURNS SETOF foo.users 以配合 RETURN QUERY。

问题 2

将您的函数声明为 STRICT(RETURNS NULL ON NULL INPUT 的同义词)然后声明 NULL 参数默认值是无稽之谈:

... firstname varchar DEFAULT NULL, IN surname varchar DEFAULT NULL)

您不能将 NULL 值传递给定义为 STRICT 的函数，它只会返回 NULL 而什么都不做。虽然 firstname 和 surname 是可选的，但不要定义函数 strict(或传递空字符串或其他东西)

更多建议

不要称你的模式为“schema”。
不要使用 reserved word user 作为标识符。尽可能使用合法的、小写的、不带引号的标识符。

函数

综合考虑，您的函数可能如下所示:

CREATE OR REPLACE FUNCTION foo.create_user_with_login (_email text
                                                     , _password text
                                                     , _firstname text = NULL
                                                     , _surname text = NULL)

  RETURNS SETOF foo.users
  LANGUAGE plpgsql  AS  -- do *not* define it STRICT
$func$
BEGIN
   RETURN QUERY
   WITH u AS (
      INSERT INTO foo.users (firstname, surname, email)
      VALUES (_firstname, _surname, _email)
      RETURNING *
      )
    , a AS (       -- create account using created_user.id
      INSERT INTO accounts (user_id) 
      SELECT u.user_id FROM u
      )
      --  more chained CTEs with DML statements?
   TABLE u;        -- return the initially created user
END
$func$;

是的，这是一个单个 SQL 语句，带有几个数据修改 CTE 来完成这一切。最快最干净。为方便起见，函数包装器是可选的。也可能是 LANGUAGE sql。相关:

• Insert data in 3 tables at a time using Postgres

我在函数参数名称前加上下划线 (_email) 以排除命名约定。这完全是可选的，但如果不这样做，您已经仔细跟踪了冲突参数、变量和列名的范围。

TABLE u 是 SELECT * FROM u 的缩写。

• Is there a shortcut for SELECT * FROM?

将查询结果存储在 plpgsql 变量中？

三种不同的情况:

1. 单值:

   • Store query result in a variable using in PL/pgSQL

2. 单行

   • Declare row type variable in PL/pgSQL

3. 行集(= 表)

没有“表变量”，但有几个其他选项:

• PostgreSQL table variable
• How to use a record type variable in plpgsql?