PostgreSQL pg_bulkload 导入 FUNCTION 数据

1 背景知识

本文主要介绍如何使用 pg_bulkload 导入函数所生成的数据。

2 创建函数

DROP TYPE IF EXISTS sample_type CASCADE;
CREATE TYPE sample_type AS (sum integer, name char(23));
CREATE OR REPLACE FUNCTION sample_function() RETURNS SETOF sample_type
    AS $ SELECT salary, upper(last_name) FROM hr.employees $
    LANGUAGE SQL;

3 创建表

创建用于装载 csv 数据的表 zlibrary.isbn。

psql -U postgres -d testdb

DROP TABLE IF EXISTS sample_table;
CREATE TABLE sample_table (
    salary integer NOT NULL,
    last_name character varying(25) NOT NULL
);

4 创建控制文件

vi /soft/sample_func.ctl

TABLE = sample_table
TYPE = FUNCTION
WRITER = DIRECT
INPUT = sample_function()          # if to use the user-defined function
#INPUT = generate_series(1, 1000)  # if to use the build-in function, which generate sequential numbers from 1 to 1000

对于以上参数详细信息，请参考 pg_bulkload 控制文件参数。

5 导入数据

单进程导入数据的耗时为 1m14.976s。

time pg_bulkload -U postgres -d testdb /soft/sample_func.ctl

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        107 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

real    0m0.019s
user    0m0.001s
sys     0m0.008s

6 查看导入的数据

SELECT * FROM sample_table ;

//屏幕输出：
salary |        last_name        
--------+-------------------------
  24000 | KING                   
  17000 | KOCHHAR                
  17000 | DE HAAN                
  ........... 
  ........
  .....  
  .. 
  12000 | HIGGINS                
   8300 | GIETZ                  
(107 rows)