Sunday, November 7, 2010

Making Dynamic Copy Command

There was a Question put by one of user about loading data in PostgreSQL skipping column which has default value using Copy Command.

With above requirement, he has also mentioned that table’s column get changed and default values too.

So, he wanted a plpgsql function code, which can be use for any table and file to load the data.

Following is a sample plpgsql code which can be use to make things happen:
CREATE OR REPLACE FUNCTION Copy_test(text,text,text) returns boolean
AS
$$
 DECLARE
    rec record;
    cmd text;
    colstring text;
 BEGIN
        cmd:='select array_to_string(array(select column_name::text from information_schema.columns where table_name='||''''||$2||''''||' and table_schema='||''''||$1||''''||' and column_default is null),'||''''||','||''''||')';
         EXECUTE  cmd into colstring;
 cmd:='COPY "'||$1||'"."'||$2||'"('||colstring||') from '||''''||$3||'''';
        EXECUTE  cmd;
     EXCEPTION when others then
 return false;
 Return true;
  END;
$$ language plpgsql;
Usage:
select copy_test('schemaname','tablename','filename');

Usage Example is given below:
postgres=# select * from test_copy;
 id | id1 
----+-----
(0 rows)

postgres=#select Copy_test('public','test_copy','/Users/postgres/test_data');
 copy_test 
-----------
 t
(1 row)

postgres=# select * from test_copy;                                                                                                                                      id | id1 
----+-----
  1 |   1
  2 |   2
  3 |   3
  4 |   4
  5 |   5
(5 rows)

No comments:

Post a Comment