CODEWITHSUNDEEP
postgresqlsql-insertsql-function
Shevliaskovic
Shevliaskovic

Reputation: 1564

Create a function to insert into a table from another table in PostgreSQL

I have created some types and then a table that has these types:

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

Also, I have another table, named customers that has some data in it. What I want to do is to create a function that will copy all the elements from customers into customers_1. The columns of customers are the same as customers_1,but on customers_1 I have created types that include some of the columns.

('Customers' has 20 columns, and I have broken that into the 6 types that are on Customers_1).

Here is my function:

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;

I'm not sure if the new is correct. The error I get is 

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

Update - I tried using the ROW syntax:

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;

I execute the Function, and I get an error: 

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

Upvotes: 1

Views: 4169

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

Couple of problems.

Insert Into Customers_1(
Select

You don't need the paren there, it's just

INSERT INTO customers_1 SELECT ....

As for this:

NEW Data_typ (username, password),

are you just trying to create a Data_typ, where Data_typ is a composite type created with CREATE TYPE Data_typ AS (...) ?

If so, you want:

ROW(username, password)::xy

example, given 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)

Upvotes: 3

Related Questions