• PostgreSQL dblink使用过程


    安装:

    进入/root/postgresql-11.2/contrib/dblink

    make && make install

    切换到postgres用户

    [root@fce40690-0e46-4603-e80e-ca351bda31ec dblink]# su - postgres

    上一次登录:三 5月  8 03:47:54 UTC 2019pts/1 上

    [postgres@fce40690-0e46-4603-e80e-ca351bda31ec ~]$ psql

    psql (11.2)

    Type "help" for help.

    postgres=# create extension dblink;

    ERROR:  extension "dblink" already exists

    使用:

    postgres=# select dblink_connect('mydb', 'dbname=mydb host=localhost');

     dblink_connect

    ----------------

     OK

    (1 row)

    postgres=# select * from dblink('mydb', 'select * from test') as test(id integer, info varchar(8));

    ERROR:  value too long for type character varying(8)

    postgres=# select * from dblink('mydb', 'select * from test') as test(id integer, info varchar(32));

     id |               info

    ----+----------------------------------

      1 | c53bc2b29be0ef76d863a53efed13ea1

      2 | 28fd974058eb778168482942c8894004

      3 | 6e90dbdf605adffe70761aa8521ab626

      4 | c32edb06e0f7e0067322f346ec60e663

      5 | 2eb0c9fcb931ede7440244745c686177

      6 | d34a41454758897e9b9d0c2dd1a972ae

      7 | 09d8e45db5f32a20e4e77ed51cf34b1a

      8 | 86cb5c36edda6fffd9f9c75aa9c3f0fe

      9 | 7ae28a3b304291dbcb96d19b92b42012

     10 | 581fddcce1c9e66a42ae4fad8a604e0d

    (10 rows)

    postgres=# select dblink_get_connections();

     dblink_get_connections

    ------------------------

     {mydb}

    (1 row)

    创建视图,来固化查询

    postgres=# create view test_mydb as select * from dblink('mydb', 'select * from test') as test(id integer, info varchar(32));

    CREATE VIEW

    postgres=# select dblink_connect('mydb', 'dbname=mydb host=localhost');

     dblink_connect

    ----------------

     OK

    (1 row)

    postgres=# select * from test_mydb;

     id |               info

    ----+----------------------------------

      1 | c53bc2b29be0ef76d863a53efed13ea1

      2 | 28fd974058eb778168482942c8894004

      3 | 6e90dbdf605adffe70761aa8521ab626

      4 | c32edb06e0f7e0067322f346ec60e663

      5 | 2eb0c9fcb931ede7440244745c686177

      6 | d34a41454758897e9b9d0c2dd1a972ae

      7 | 09d8e45db5f32a20e4e77ed51cf34b1a

      8 | 86cb5c36edda6fffd9f9c75aa9c3f0fe

      9 | 7ae28a3b304291dbcb96d19b92b42012

     10 | 581fddcce1c9e66a42ae4fad8a604e0d

    (10 rows)

    执行没有返回结果的SQL

    postgres=# select dblink_exec('mydb', 'insert into test values(1, ''dblink insert i am'')');

     dblink_exec

    -------------

     INSERT 0 1

    (1 row)

    fetch的方式分页获取数据:

    postgres=# select dblink_open('mydb', 'foo', 'select * from test');

     dblink_open

    -------------

     OK

    (1 row)

                                                         ^

    postgres=# select * from dblink_fetch('mydb', 'foo', 3) as test(id int, info text);

     id |               info

    ----+----------------------------------

      1 | c53bc2b29be0ef76d863a53efed13ea1

      2 | 28fd974058eb778168482942c8894004

      3 | 6e90dbdf605adffe70761aa8521ab626

    (3 rows)

    postgres=# select * from dblink_fetch('mydb', 'foo', 3) as (id int, info text);

     id |               info

    ----+----------------------------------

      4 | c32edb06e0f7e0067322f346ec60e663

      5 | 2eb0c9fcb931ede7440244745c686177

      6 | d34a41454758897e9b9d0c2dd1a972ae

    (3 rows)

    postgres=# (id int, info text);^C

    postgres=# select * from dblink_fetch('mydb', 'foo', 3) as (id int, info text);

     id |               info

    ----+----------------------------------

      7 | 09d8e45db5f32a20e4e77ed51cf34b1a

      8 | 86cb5c36edda6fffd9f9c75aa9c3f0fe

      9 | 7ae28a3b304291dbcb96d19b92b42012

    (3 rows)

    postgres=# select * from dblink_fetch('mydb', 'foo', 3) as (id int, info text);

     id |               info

    ----+----------------------------------

     10 | 581fddcce1c9e66a42ae4fad8a604e0d

      1 | dblink insert i am

    (2 rows)

    使用异步调用:dblink_send_querydblink_is_busydblink_get_notifydblink_get_result

    postgres=# select * from dblink_send_query('mydb', 'select * from test;');

     dblink_send_query

    -------------------

                     1

    (1 row)

    postgres=# select * from dblink_get_result('mydb') as (id int, info text);

     id |               info

    ----+----------------------------------

      1 | c53bc2b29be0ef76d863a53efed13ea1

      2 | 28fd974058eb778168482942c8894004

      3 | 6e90dbdf605adffe70761aa8521ab626

      4 | c32edb06e0f7e0067322f346ec60e663

      5 | 2eb0c9fcb931ede7440244745c686177

      6 | d34a41454758897e9b9d0c2dd1a972ae

      7 | 09d8e45db5f32a20e4e77ed51cf34b1a

      8 | 86cb5c36edda6fffd9f9c75aa9c3f0fe

      9 | 7ae28a3b304291dbcb96d19b92b42012

     10 | 581fddcce1c9e66a42ae4fad8a604e0d

      1 | dblink insert i am

    (11 rows)

    postgres=# select * from dblink_send_query('mydb', 'select * from test;');

    NOTICE:  could not send query: another command is already in progress

     dblink_send_query

    -------------------

                     0

    (1 row)

    —获取到了最后一条,再执行新的SQL才会成功

    postgres=# select * from dblink_get_result('mydb') as (id int, info text);

     id | info

    ----+------

    (0 rows)

    postgres=# select * from dblink_send_query('mydb', 'select * from test;');

     dblink_send_query

    -------------------

                     1

    (1 row)

    获取错误信息:

    postgres=# select dblink_error_message('mydb');

     dblink_error_message

    ----------------------

     OK

    (1 row)

    能否用dblink去连接oracle数据库呢?像oracle的dblink一样,连接SQL Server、MySQL、PostgreSQL?

    答案是不行的,源码里面可以看到PostgreSQL的dblink是使用的pg的c语言接口去创建连接的,而不是使用ODBC来创建:

    dblink_connect(PG_FUNCTION_ARGS)
    {
    	char	   *conname_or_str = NULL;
    	char	   *connstr = NULL;
    	char	   *connname = NULL;
    	char	   *msg;
    	PGconn	   *conn = NULL;
    	remoteConn *rconn = NULL;
    
    	dblink_init();
    
    	if (PG_NARGS() == 2)
    	{
    		conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(1));
    		connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
    	}
    	else if (PG_NARGS() == 1)
    		conname_or_str = text_to_cstring(PG_GETARG_TEXT_PP(0));
    
    	if (connname)
    		rconn = (remoteConn *) MemoryContextAlloc(TopMemoryContext,
    												  sizeof(remoteConn));
    
    	/* first check for valid foreign data server */
    	connstr = get_connect_string(conname_or_str);
    	if (connstr == NULL)
    		connstr = conname_or_str;
    
    	/* check password in connection string if not superuser */
    	dblink_connstr_check(connstr);
    	conn = PQconnectdb(connstr);
    
    	if (PQstatus(conn) == CONNECTION_BAD)
    	{
    		msg = pchomp(PQerrorMessage(conn));
    		PQfinish(conn);
    		if (rconn)
    			pfree(rconn);
    
    		ereport(ERROR,
    				(errcode(ERRCODE_SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION),
    				 errmsg("could not establish connection"),
    				 errdetail_internal("%s", msg)));
    	}
    
    	/* check password actually used if not superuser */
    	dblink_security_check(conn, rconn);
    
    	/* attempt to set client encoding to match server encoding, if needed */
    	if (PQclientEncoding(conn) != GetDatabaseEncoding())
    		PQsetClientEncoding(conn, GetDatabaseEncodingName());
    
    	if (connname)
    	{
    		rconn->conn = conn;
    		createNewConnection(connname, rconn);
    	}
    	else
    	{
    		if (pconn->conn)
    			PQfinish(pconn->conn);
    		pconn->conn = conn;
    	}
    
    	PG_RETURN_TEXT_P(cstring_to_text("OK"));
    }
    
  • 相关阅读:
    legend3---2、网站的代码里面的/也是代表根目录
    legend3---PHP使用阿里云短信服务
    legend3---laravel验证码使用
    好用网站推荐
    The practice program of C on point
    Android自己定义组件系列【9】——Canvas绘制折线图
    网络流24题 之十四 孤岛营救问题 分层图
    关于安卓你不知道的6件事
    OpenStack_Swift源代码分析——Ring基本原理及一致性Hash算法
    Codeforces554D:Kyoya and Permutation
  • 原文地址:https://www.cnblogs.com/kuang17/p/10833458.html
Copyright © 2020-2023  润新知