PostgreSQL接口编程一：OLEDB－－PGNP驱动

1介绍

PGNP Native Provider是以OLEDB接口访问PostgreSQL数据库的驱动程序。以下简称PGNP，PostgreSQL数据库以下简称pg。

 

PGNP是pg数据库的OLEDB接口驱动程序，他介于微软OLEDB和ADO.NET之OLEDB与PostgreSQL libpq库接口之间，实现了大多数OLEDB接口，并使用pg的libpq访问pg数据库。PGNP可以为.NET和NATIVE的32/64位应用程序提供支持访问pg。

 

这是一个商业软件，Business license要390$。

从http://pgoledb.com/可以下载使用版PGNP-1.3.0.2251.exe

 

 

2安装

点击PGNP-1.3.0.2251.exe开始安装，按下一步即可完成。

 

 

3用psql连到postgreSQL，psql中命令 \i sql_script_file_name即可

sql_script_file_name文件中内容如下：

 

--建模式、表、插入记录、建函数

-- Create schema for PGNP samples

 

-- DROP SCHEMA pgnp_samples;

CREATE SCHEMA pgnp_samples AUTHORIZATION postgres;

GRANT ALL ON SCHEMA pgnp_samples TO postgres;

 

SET search_path='pgnp_samples';

 

--删除photo类型字段

-- DROP TABLE pgnp_samples.contact;

CREATE TABLE contact

(

  contact_id bigint not null,

  fname character varying(64),

  lname character varying(64),

  revenue double precision,

--  photo lo,

  created_date timestamp without time zone NOT NULL DEFAULT now(),

  modified_date timestamp without time zone NOT NULL DEFAULT now(),

  CONSTRAINT pk_contact_id PRIMARY KEY (contact_id)

);

 

INSERT INTO contact(contact_id, fname, lname, revenue) VALUES (1, 'James', 'Smith', 20000.0), (2, 'Sue', 'McMartin', 35000.0);

 

-- DROP TABLE pgnp_samples."group";

CREATE TABLE "group"

(

  group_id bigint not null,

  group_name character varying(128),

  region uuid,

  created_date timestamp without time zone NOT NULL DEFAULT now(),

  modified_date timestamp without time zone NOT NULL DEFAULT now(),

  CONSTRAINT pk_group_id PRIMARY KEY (group_id)

);

 

INSERT INTO "group"(group_id, group_name, region) VALUES (1, 'EMEA', '00000000000000000000000000000001'), (2, 'NA', '00000000000000000000000000000002');

 

-- DROP FUNCTION pgnp_samples.ContactsLike(character varying(64));

CREATE OR REPLACE FUNCTION ContactsLike(character varying(64)) RETURNS bigint AS 'SELECT count(1) FROM contact WHERE lname ~~* $1;' LANGUAGE SQL;

 

--as后边加空格

 DROP FUNCTION pgnp_samples.sptest2(integer);

CREATE OR REPLACE FUNCTION pgnp_samples.sptest2(IN integer)

  RETURNS TABLE(f1 integer, f2 text) AS 

$BODY$

  SELECT $1, CAST($1 AS text) || ' is text'

  UNION ALL

  SELECT $1*2, CAST($1 AS text) || ' is text too'    

$BODY$

  LANGUAGE 'sql';

 

   

-- DROP FUNCTION pgnp_samples.GetMultipleResults();

CREATE OR REPLACE FUNCTION GetMultipleResults() RETURNS SETOF refcursor AS

'DECLARE refContact refcursor; refGroup refcursor;

BEGIN

  OPEN refContact FOR SELECT * FROM contact; RETURN NEXT refContact;

  OPEN refGroup FOR SELECT * FROM "group"; RETURN NEXT refGroup;

  RETURN;

END;' LANGUAGE plpgsql;

 

-- DROP TABLE arrays

CREATE TABLE arrays

(

  id serial NOT NULL,

  test1d character varying(15)[],

  test2d numeric(7,3)[][],

  test3d integer[][][],

  C