pgsql thinkphp5_thinkphp5, 結合pgsql使用時, 要先運行這段sql代碼

1 CREATE OR REPLACE FUNCTION pgsql_type(a_type varchar) RETURNS varchar AS

2 $BODY$3 DECLARE

4 v_type varchar;5 BEGIN

6 IF a_type='int8' THEN

7 v_type:='bigint';8 ELSIF a_type='int4' THEN

9 v_type:='integer';10 ELSIF a_type='int2' THEN

11 v_type:='smallint';12 ELSIF a_type='bpchar' THEN

13 v_type:='char';14 ELSE

15 v_type:=a_type;16 END IF;17 RETURNv_type;18 END;19 $BODY$20 LANGUAGE PLPGSQL;21

22 CREATE TYPE "public"."tablestruct" AS(23 "fields_key_name" varchar(100),24 "fields_name" VARCHAR(200),25 "fields_type" VARCHAR(20),26 "fields_length" BIGINT,27 "fields_not_null" VARCHAR(10),28 "fields_default" VARCHAR(500),29 "fields_comment" VARCHAR(1000)30 );31

32 CREATE OR REPLACE FUNCTION "public"."table_msg" (a_schema_name varchar, a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS

33 $body$34 DECLARE

35 v_ret tablestruct;36 v_oid oid;37 v_sql varchar;38 v_rec RECORD;39 v_key varchar;40 BEGIN

41 SELECT

42 pg_class.oid INTOv_oid43 FROM

44 pg_class45 INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) =a_schema_name)46 WHERE

47 pg_class.relname=a_table_name;48 IF NOT FOUND THEN

49 RETURN;50 END IF;51

52 v_sql='

53 SELECT54 pg_attribute.attname AS fields_name,55 pg_attribute.attnum AS fields_index,56 pgsql_type(pg_type.typname::varchar) AS fields_type,57 pg_attribute.atttypmod-4 as fields_length,58 CASE WHEN pg_attribute.attnotnull THEN''not null''

59 ELSE''''

60 END AS fields_not_null,61 pg_attrdef.adsrc AS fields_default,62 pg_description.description AS fields_comment63 FROM64 pg_attribute65 INNER JOIN pg_class ON pg_attribute.attrelid = pg_class.oid66 INNER JOIN pg_type ON pg_attribute.atttypid = pg_type.oid67 LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum68 LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum69 WHERE70 pg_attribute.attnum > 071 AND attisdropped <>''t''

72 AND pg_class.oid =' || v_oid || '

73 ORDER BY pg_attribute.attnum';74

75 FOR v_rec IN EXECUTEv_sql LOOP76 v_ret.fields_name=v_rec.fields_name;77 v_ret.fields_type=v_rec.fields_type;78 IF v_rec.fields_length > 0 THEN

79 v_ret.fields_length:=v_rec.fields_length;80 ELSE

81 v_ret.fields_length:=NULL;82 END IF;83 v_ret.fields_not_null=v_rec.fields_not_null;84 v_ret.fields_default=v_rec.fields_default;85 v_ret.fields_comment=v_rec.fields_comment;86 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;87 IF FOUND THEN

88 v_ret.fields_key_name=v_key;89 ELSE

90 v_ret.fields_key_name='';91 END IF;92 RETURN NEXTv_ret;93 ENDLOOP;94 RETURN;95 END;96 $body$97 LANGUAGE 'plpgsql' VOLATILE CALLED ON NULLINPUT SECURITY INVOKER;98

99 COMMENT ON FUNCTION "public"."table_msg"(a_schema_name varchar, a_table_name varchar)100 IS '獲得表信息';101

102 ---重載一個函數

103 CREATE OR REPLACE FUNCTION "public"."table_msg" (a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS

104 $body$105 DECLARE

106 v_ret tablestruct;107 BEGIN

108 FOR v_ret IN SELECT * FROM table_msg('public',a_table_name) LOOP109 RETURN NEXTv_ret;110 ENDLOOP;111 RETURN;112 END;113 $body$114 LANGUAGE 'plpgsql' VOLATILE CALLED ON NULLINPUT SECURITY INVOKER;115

116 COMMENT ON FUNCTION "public"."table_msg"(a_table_name varchar)117 IS '獲得表信息';