创建一个函数以从PostgreSQL中的另一个表插入一个表

Shevliaskovic 发表于 Dev

舍夫利亚斯科维奇

我创建了一些类型，然后创建了具有这些类型的表：

Create Type Data_typ AS (
... );

Create Type Communication_typ As (
...;

Create Type CreditCard_typ As (
...);

Create Type Name_typ As (
...);

Create Type Personal_typ As (
...);

Create Type Address_typ As (
...);

Create Type Customers_typ As (
CustomerID integer,
Data Data_typ,
Communication Communication_typ,
CreditCard CreditCard_typ,
Name Name_typ,
Personal Personal_typ,
Address Address_typ);

Create Table Customers_1 of Customers_typ(
primary key (CustomerID));

另外，我还有另一个表，名为表，customers其中包含一些数据。我想要做的是创建一个函数，将所有元素从客户复制到客户_1。的列customers与customers_1相同，但是在customers_1上，我创建了包含某些列的类型。

（“客户”有20列，我将其分为“客户_1”上的6种类型）。

这是我的功能：

Create OR REPLACE Function CopyCustomers() RETURNS VOID AS
$$
Begin 
    Insert Into Customers_1(
    Select
    NEW Data_typ (username, password),
    new communication_typ(email,phone),
    new creditCard_typ(creditcardtyp,creditcard,creditcardexpiration),
    new name_typ(firstname,lastname),
    new personal(age,income,gender),
    new address(address1,address2,coty,state,zip)
    From Customers);

End;
$$  LANGUAGE plpgsql;

我不确定是否new正确。我得到的错误是

ERROR:  syntax error at or near "("
LINE 7:  NEW Data_typ (username, password),
                      ^
********** Error **********

ERROR: syntax error at or near "("
SQL state: 42601
Character: 119

更新-我尝试使用ROW语法：

Create OR REPLACE Function CopyCustomers() RETURNS VOID AS
$$
Begin 
    Insert Into Customers_1
    Select
    ROW(username, password),
    ROW(email,phone),
    ROW(creditcardtyp,creditcard,creditcardexpiration),
    ROW(firstname,lastname),
    ROW(age,income,gender),
    ROW(address1,address2,coty,state,zip)
    From Customers;

End;
$$  LANGUAGE plpgsql;

我执行函数，然后收到错误消息：

ERROR:  cannot cast type record to data_typ
LINE 4:  ROW(username, "Password"),
         ^
QUERY:  Insert Into Customers_1
    Select
    CustomerID,
    ROW(username, password),
    ROW(email,phone),
    ROW(creditcardcype,creditcard,creditcardexpiration),
    ROW(firstname,lastname),
    ROW(age,income,gender),
    ROW(address1,address2,city,state,zip)
    From CustomerS
CONTEXT:  PL/pgSQL function copycustomers() line 3 at SQL statement
********** Error **********

ERROR: cannot cast type record to data_typ
SQL state: 42846
Context: PL/pgSQL function copycustomers() line 3 at SQL statement

克雷格·林格

几个问题。

Insert Into Customers_1(
Select

您不需要那里的父母，这只是

INSERT INTO customers_1 SELECT ....

至于：

NEW Data_typ (username, password),

您是否只是尝试创建一个Data_typ，在其中创建Data_typ一个复合类型CREATE TYPE Data_typ AS (...)？

如果是这样，您要：

ROW(username, password)::xy

例如，给出CREATE TYPE xy AS (x integer, y integer);：

regress=> SELECT new xy(1,2);
ERROR:  syntax error at or near "("
LINE 1: SELECT new xy(1,2);
                     ^
regress=> SELECT xy(1,2);
ERROR:  function xy(integer, integer) does not exist
LINE 1: SELECT xy(1,2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
regress=> SELECT ROW(1,2)::xy;
  row  
-------
 (1,2)
(1 row)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。