thinkphp 串連postgresql

標籤：

PHP串連：

php.ini中將extension=php_pgsql.dll前面的分號去掉，然後設定extension_dir指向php檔案下的ext檔案夾。

正常情況下這樣php就可以串連postgres了，但是我的仍然出錯，提示“Fatal error: Call to undefined function pg_connect()”，查看Apache的日誌，“PHP Startup: Unable to load dynamic library ‘****\\PHP5\\ext\\php_pdo_pgsql.dll”。

經過尋找，在Apache中conf檔案夾下的設定檔httpd.conf中添加 LoadFile "***/PHP5/libpq.dll"，位置在LoadModule後面即可。

（***為php安裝路徑）

 

 

重點來了:thinkphp 中的table_msg函數竟然找不到，需要自己手動建立:

在 PostgreSQL 裡添加自訂函數  table_msg

複製以下代碼到SQL管理器裡執行即可：

CREATE OR REPLACE FUNCTION pgsql_type(a_type varchar) RETURNS varchar AS$BODY$DECLARE     v_type varchar;BEGIN     IF a_type=‘int8‘ THEN          v_type:=‘bigint‘;     ELSIF a_type=‘int4‘ THEN          v_type:=‘integer‘;     ELSIF a_type=‘int2‘ THEN          v_type:=‘smallint‘;     ELSIF a_type=‘bpchar‘ THEN          v_type:=‘char‘;     ELSE          v_type:=a_type;     END IF;     RETURN v_type;END;$BODY$LANGUAGE PLPGSQL;CREATE TYPE "public"."tablestruct" AS (  "fields_key_name" varchar(100),  "fields_name" VARCHAR(200),  "fields_type" VARCHAR(20),  "fields_length" BIGINT,  "fields_not_null" VARCHAR(10),  "fields_default" VARCHAR(500),  "fields_comment" VARCHAR(1000));CREATE OR REPLACE FUNCTION "public"."table_msg" (a_schema_name varchar, a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS$body$DECLARE     v_ret tablestruct;     v_oid oid;     v_sql varchar;     v_rec RECORD;     v_key varchar;BEGIN     SELECT           pg_class.oid  INTO v_oid     FROM           pg_class           INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) = a_schema_name)     WHERE           pg_class.relname=a_table_name;     IF NOT FOUND THEN         RETURN;     END IF;     v_sql=‘     SELECT           pg_attribute.attname AS fields_name,           pg_attribute.attnum AS fields_index,           pgsql_type(pg_type.typname::varchar) AS fields_type,           pg_attribute.atttypmod-4 as fields_length,           CASE WHEN pg_attribute.attnotnull  THEN ‘‘not null‘‘           ELSE ‘‘‘‘           END AS fields_not_null,           pg_attrdef.adsrc AS fields_default,           pg_description.description AS fields_comment     FROM           pg_attribute           INNER JOIN pg_class  ON pg_attribute.attrelid = pg_class.oid           INNER JOIN pg_type   ON pg_attribute.atttypid = pg_type.oid           LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum           LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum     WHERE           pg_attribute.attnum > 0           AND attisdropped <> ‘‘t‘‘           AND pg_class.oid = ‘ || v_oid || ‘     ORDER BY pg_attribute.attnum‘ ;     FOR v_rec IN EXECUTE v_sql LOOP         v_ret.fields_name=v_rec.fields_name;         v_ret.fields_type=v_rec.fields_type;         IF v_rec.fields_length > 0 THEN            v_ret.fields_length:=v_rec.fields_length;         ELSE            v_ret.fields_length:=NULL;         END IF;         v_ret.fields_not_null=v_rec.fields_not_null;         v_ret.fields_default=v_rec.fields_default;         v_ret.fields_comment=v_rec.fields_comment;         SELECT constraint_name INTO v_key FROM information_schema.key_column_usage WHERE table_schema=a_schema_name AND table_name=a_table_name AND column_name=v_rec.fields_name;         IF FOUND THEN            v_ret.fields_key_name=v_key;         ELSE            v_ret.fields_key_name=‘‘;         END IF;         RETURN NEXT v_ret;     END LOOP;     RETURN ;END;$body$LANGUAGE ‘plpgsql‘ VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;COMMENT ON FUNCTION "public"."table_msg"(a_schema_name varchar, a_table_name varchar)IS ‘獲得表資訊‘;---重載一個函數CREATE OR REPLACE FUNCTION "public"."table_msg" (a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS$body$DECLARE    v_ret tablestruct;BEGIN    FOR v_ret IN SELECT * FROM table_msg(‘public‘,a_table_name) LOOP        RETURN NEXT v_ret;    END LOOP;    RETURN;END;$body$LANGUAGE ‘plpgsql‘ VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;COMMENT ON FUNCTION "public"."table_msg"(a_table_name varchar)IS ‘獲得表資訊‘;

 

 

修改 ThinkPHP 原始碼裡面的 BUG

把 ThinkPHP\Library\Think\Db\Driver\Pgsql.class.php 第 41 行 修改為：

$result =   $this->query(‘select fields_name as "field",fields_type as "type",fields_not_null as "null",fields_key_name as "key",fields_default as "default",fields_default as "extra" from table_msg(\‘‘.$tableName.‘\‘);‘);

也就是在 $tableName 前後各添加一個單引號，(‘.$tableName.‘) => (\‘‘.$tableName.‘\‘)。

 

除了上面的問題，我還發現，M(‘table‘)->add()不能正常的返回新增記錄的主鍵id值。

thinkphp 串連postgresql