postgresql - 向不同表中插入数据的函数

Question

我在 PostgreSQL 中有三个表:

CREATE TABLE organization (id int, name text, parent_id int);

CREATE TABLE staff (id int, name text, family text, organization_id int); 

CREATE TABLE clock(id int, staff_id int, Date date, Time time);

我需要一个函数来获取这些表的所有字段作为输入(总共 8 个)，然后将这些输入插入到表的适当字段中

这是我的代码:

CREATE FUNCTION insert_into_tables(org_name character varying(50), org_PID int, person_name character varying(50),_family character varying(50), org_id int, staff_id int,_date date, _time time without time zone) 
RETURNS void AS $$
BEGIN

INSERT INTO "Org".organisation("Name", "PID")
VALUES ($1, $2);
INSERT INTO "Org".staff("Name", "Family", "Organization_id")
VALUES ($3, $4, $5);
INSERT INTO "Org"."Clock"("Staff_Id", "Date", "Time")
VALUES ($6, $7, $8);

END;
$$ LANGUAGE plpgsql;

select * from insert_into_tables('SASAD',9,'mamad','Imani',2,2,1397-10-22,'08:26:47')

但是没有插入数据。我收到错误:

ERROR: function insert_into_tables(unknown, integer, unknown, unknown, integer, integer, integer, unknown) does not exist

LINE 17: select * from insert_into_tables('SASAD',9,'mamad','Imani',2... ^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

我哪里错了？

Answer 1

那是因为倒数第二个参数被声明为date，而不是int。你忘记了单引号:

select * from insert_into_tables('SASAD',9,'mamad','Imani',2,2,<b>'</b>1397-10-22<b>'</b>,'08:26:47');

如果没有单引号，这将被解释为 3 个 integer 常量之间的减法，得到一个 integer:1397-10-22 = 1365 .

同时修复您的标识符:双引号保留大写字母，因此 "Name" 与 name 等不同。参见:

• Are PostgreSQL column names case-sensitive?