On Wed, Feb 5, 2014 at 7:53 AM, George Ant <g.antonopoulos...@gmail.com>wrote:
> Hey Guys, > > I am trying to copy data from one table to another using plpgsql. The two > tables have different structure cause the new one is object-table. My > function is this : > > CREATE OR REPLACE FUNCTION copy_customers() RETURNS integer as $BODY$ > DECLARE > idcounter integer := 1; > firstname text; > lastname text; > address1 text; > address2 text; > city text; > state text; > zip text; > country text; > region text; > BEGIN > FOR idcounter In 1..20000 > LOOP > > -- Add the values into the variables. > SELECT > > "FirstName","LastName","Address1","Address2","City","State","Zip","Country","Region" > INTO firstname, > lastname,address1,address2,city,state,zip,country,region > FROM "Customers" > WHERE "CustomerId" = idcounter; > > --Insert the variables to the new table. > INSERT INTO "Customers_object_table" (customerid , firstname, > lastname, > address) > > VALUES(idcounter,firstname,lastname,(address1,address2,city,state,zip,country,region)); > END Loop; > > return 1; > END; > $BODY$ > LANGUAGE plpgsql; > > > This function is working fine, but the problem is that the table > "Customers" > has more than 20 columns, so the code is ugly and unmaintainable. Also I > want to do the same job for 10 more tables. > > Can somebody help me to change this function in a way that I won't have to > declare the columns? > > Notice that the destination-tables are object tables and can be different > than the old tables, so I am not sure if what I ask is possible. > > Kind Regards, > George Ant > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/PL-pgSQL-Copy-data-from-one-table-to-another-tp5790663.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > I assume you created a composite type (addresstype) in Customers_object_table. CREATE OR REPLACE FUNCTION copy_customers() RETURNS integer as $BODY$ BEGIN INSERT INTO Customers_object_table (customerid, firstname, lastname, address) SELECT c.customerid, c.firstname, c.lastname, (c.address1, c.address2, c.city, c.zip, c.country, c.region)::addresstype FROM Customers c WHERE c.customerid >= 1 AND c.customerid <= 20000; RETURN 1; END $BODY$ LANGUAGE plpgsql;
