Re: [GENERAL] PL/pgSQL Copy data from one table to another

[Elliot]

On 2014-02-05 10:36, ChoonSoo Park wrote:
On Wed, Feb 5, 2014 at 7:53 AM, George Ant 
<g.antonopoulos...@gmail.com <mailto: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
    <mailto: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;
There's also no need for pgsql at that point - a straight up sql 
function would suffice.