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

Question

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

While it's good to edit your questions in response to new information, please don't replace what was already there. Now existing answers like mine make no sense; I seem to be talking about something completely different to what you wrote. I fixed it for you. — Craig Ringer
– Craig Ringer, Commented Feb 3, 2014 at 9:41
You still have the SELECT in parentheses. That doesn't make sense. remove them - the ( after Customers_, and the ) before the ; should both be removed. Then run it in psql. Show how you execute the function too. — Craig Ringer
– Craig Ringer, Commented Feb 3, 2014 at 9:44
OK, now, read my answer again. What did I say, after I already told you about the parentheses, when I mentioned casts? See how I cast the ROW(..) to the target type for the field? — Craig Ringer
– Craig Ringer, Commented Feb 3, 2014 at 12:06
Okay, now this solved my problem, but I get another error ERROR: value too long for type character(2). I saw some other questions about this,but couldn't solve it. I think I'll ask a different question about this, cos it's a different problem — Shev
– Shev, Commented Feb 3, 2014 at 16:38

Craig Ringer · Accepted Answer · 2014-02-03 02:09:38Z

3

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)

answered Feb 3, 2014 at 2:09

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shev Over a year ago

Do I need to create type if I have the Row? I'm confused where to put row

Craig Ringer Over a year ago

@Shevliaskovic Well, you wrote new communication_typ(email, phone). That's nonsense syntax. Instead use ROW(email, phone); PostgreSQL should figure out that the destination column is of type communication_typ; if it doesn't, explicitly cast the row to communication_typ.

Shev Over a year ago

Okay about that. But the insert doesn't work. I wrote the row and then executed it.After that I executed Select * From customers_1;,but I don't get any result. The table is empty.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related