如何从 PostgreSQL 中的插入存储过程返回字符串 ID

Question

我正在尝试在插入语句在存储过程中运行后返回字符串 id。

这是我的存储过程代码：

CREATE OR REPLACE FUNCTION create_quote(
    p_id character varying(40),
    p_name character varying(200),
    p_website character varying(200),
    p_phone character varying(100),
    p_email character varying(200),
    p_msg text,
    p_created_on timestamp without time zone,
    p_created_ip cidr,
    p_contacted boolean,
    p_contacted_on timestamp without time zone
)
RETURNS character varying(40) AS $body$
BEGIN
    INSERT INTO quote VALUES (p_id, p_name, p_website, p_phone, p_email, p_msg, p_created_on, p_created_ip, p_contacted, p_contacted_on)
    ON CONFLICT (id)
       DO UPDATE SET name = p_name, website = p_website, phone = p_phone, email = p_email, msg = p_msg, created_on = p_created_on, created_ip = p_created_ip, contacted 
= p_contacted, contacted_on = p_contacted_on RETURNING id;
END;
$body$ LANGUAGE plpgsql;

表架构：

                               Table "public.quote"
    Column    |            Type             |              Modifiers               
--------------+-----------------------------+--------------------------------------
 id           | character varying(40)       | not null
 name         | character varying(200)      | not null
 website      | character varying(200)      | not null
 phone        | character varying(100)      | not null
 email        | character varying(200)      | not null
 msg          | text                        | 
 created_on   | timestamp without time zone | default timezone('utc'::text, now())
 created_ip   | cidr                        | 
 contacted    | boolean                     | 
 contacted_on | timestamp without time zone | 
Indexes:
    "quote_pkey" PRIMARY KEY, btree (id)
    "quote_email_key" UNIQUE CONSTRAINT, btree (email)

这是我尝试从 psql 客户端调用该过程时遇到的错误。

select * from create_quote("qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1", "name", "website", "phone", "email", "msg", now()::timestamp, NULL, 0, now()::timestamp);
ERROR:  column "qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1" does not exist
LINE 1: select * from create_quote("qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q...

我有点困惑，因为我没有传入列，只是为第一个参数传递一个字符串id。我做错了什么？

我进行了克莱门斯下面提到的更改，但现在出现此错误：

select create_quote('qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1', 'name', 'website', 'phone', 'email', 'msg', now()::timestamp, NULL, 0, now()::timestamp);
ERROR:  function create_quote(unknown, unknown, unknown, unknown, unknown, unknown, timestamp without time zone, unknown, integer, timestamp without time zone) does not exist
LINE 1: select create_quote('qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1', 'na...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

谢谢

Answer 1

代码中有几个错误：

1. SQL 中的字符串使用单引号进行限制。双引号用于保护标识符(例如区分大小写的列名(。例如，使用'qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1'而不是"qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1"。

2. 您的函数返回的是标量而不是集合。您不应该从FROM子句调用它。

   SELECT create_quote('qw2e3r4t5y6u7i8o9i8u7y6t5r4e3w2q1', 'name', 'website', 'phone', 'email', 'msg', now()::timestamp, NULL, 0, now()::timestamp);
   

3. 函数不返回值。您可以将 UPSERT 中的结果分配给变量并返回此结果。或者更容易返回输入参数p_id，因为您的 UPSERT 将始终返回此值。

   CREATE OR REPLACE FUNCTION create_quote(
       p_id character varying(40),
       p_name character varying(200),
       p_website character varying(200),
       p_phone character varying(100),
       p_email character varying(200),
       p_msg text,
       p_created_on timestamp without time zone,
       p_created_ip cidr,
       p_contacted boolean,
       p_contacted_on timestamp without time zone
   )
   RETURNS character varying(40) AS $body$
   BEGIN
       INSERT INTO quote VALUES (p_id, p_name, p_website, p_phone, p_email, p_msg, p_created_on, p_created_ip, p_contacted, p_contacted_on)
       ON CONFLICT (id)
           DO UPDATE SET name = p_name, website = p_website, phone = p_phone, email = p_email, msg = p_msg, created_on = p_created_on, created_ip = p_created_ip, contacted 
   = p_contacted, contacted_on = p_contacted_on;
       RETURN p_id;
   END;
   $body$ LANGUAGE plpgsql;