修复 ThinkPHP5 使用 PostgreSQL 12+ 高版本报错

ThinkPHP 使用 PostgreSQL 需要导入一个 pgsql.sql ，然而在PostgreSQL 12版本以后，pg_attrdef.adsrc 被移除了，因此会报错

将 pgsql.sql 中的

pg_attrdef.adsrc

1

替换为

pggetexpr(pgattrdef.adbin, pgattribute.attrelid)

1

然后重新运行一次SQL即可

附：替换完成后的pgsql.sql内容

CREATE OR REPLACE FUNCTION "public"."tablemsg" (aschemaname varchar, atable_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

pgclass.oid INTO void

FROM

pg_class

INNER JOIN pgnamespace ON (pgclass.relnamespace = pgnamespace.oid AND lower(pgnamespace.nspname) = aschemaname)

WHERE

pgclass.relname=atable_name;

IF NOT FOUND THEN

RETURN;

END IF;

v_sql='

SELECT

pgattribute.attname AS fieldsname,

pgattribute.attnum AS fieldsindex,

pgsqltype(pgtype.typname::varchar) AS fields_type,

pgattribute.atttypmod-4 as fieldslength,

CASE WHEN pg_attribute.attnotnull THEN ''not null''

ELSE ''''

END AS fieldsnotnull,

pggetexpr(pgattrdef.adbin, pgattribute.attrelid) AS fields_default,

pgdescription.description AS fieldscomment

FROM

pg_attribute

INNER JOIN pgclass ON pgattribute.attrelid = pg_class.oid

INNER JOIN pgtype ON pgattribute.atttypid = pg_type.oid

LEFT OUTER JOIN pgattrdef ON pgattrdef.adrelid = pgclass.oid AND pgattrdef.adnum = pg_attribute.attnum

LEFT OUTER JOIN pgdescription ON pgdescription.objoid = pgclass.oid AND pgdescription.objsubid = pg_attribute.attnum

WHERE

pg_attribute.attnum > 0

AND attisdropped <> ''t''

AND pgclass.oid = ' || void || '

ORDER BY pg_attribute.attnum' ;

FOR vrec IN EXECUTE vsql LOOP

vret.fieldsname=vrec.fieldsname;

vret.fieldstype=vrec.fieldstype;

IF vrec.fieldslength > 0 THEN

vret.fieldslength:=vrec.fieldslength;

ELSE

vret.fieldslength:=NULL;

END IF;

vret.fieldsnotnull=vrec.fieldsnotnull;

vret.fieldsdefault=vrec.fieldsdefault;

vret.fieldscomment=vrec.fieldscomment;

SELECT constraintname INTO vkey FROM informationschema.keycolumnusage WHERE tableschema=aschemaname AND tablename=atablename AND columnname=vrec.fieldsname;

IF FOUND THEN

vret.fieldskeyname=vkey;

ELSE

vret.fieldskey_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"."tablemsg"(aschemaname varchar, atable_name varchar)

IS '获得表信息';

\---重载一个函数

CREATE OR REPLACE FUNCTION "public"."tablemsg" (atable_name varchar) RETURNS SETOF "public"."tablestruct" AS

$body$

DECLARE

v_ret tablestruct;

BEGIN

FOR vret IN SELECT * FROM tablemsg('public',atablename) LOOP

RETURN NEXT v_ret;

END LOOP;

RETURN;

END;

$body$

LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

COMMENT ON FUNCTION "public"."tablemsg"(atable_name varchar)

IS '获得表信息';