最近开始研究PostgreSQL的相关知识，写了一个简单的存储结构，慢慢的发现PostgreSQL还是很值得去研究的
www.2cto.com
--
-- 建立小时记录与日记录的关联规则
DROP FUNCTION IF EXISTS group_tbl_moburlvisit
(name, timestamp);
CREATE FUNCTION group_tbl_moburlvisit
(
_tgrp name,
_tick timestamp
)
RETURNS name
AS $$
DECLARE
_strtick name;
_grpname name;
_tbregex name;
_subtbl name;
_grps name;
_gfmt name;
_tfmt name;
_tmp integer;
_cursor refcursor;
BEGIN
-- 确保只能从比汇聚时间小一个级别的数据中进行汇聚
IF _tgrp = 'H1' THEN _grps = '1Hour'; _gfmt = 'YYYYMMDDHH24'; _tfmt = 'YYYYMMDDHH24..';
ELSIF _tgrp = 'D1' THEN _grps = '1Day'; _gfmt = 'YYYYMMDD'; _tfmt = 'YYYYMMDD..';
ELSIF _tgrp = 'M1' THEN _grps = '1Month'; _gfmt = 'YYYYMM'; _tfmt = 'YYYYMM..';
ELSIF _tgrp = 'Y1' THEN _grps = '1Year'; _gfmt = 'YYYY'; _tfmt = 'YYYY..';
END IF;
_grpname := 'tbl_moburlvisit_' || to_char(_tick, _gfmt);
_tbregex := 'tbl_moburlvisit_' || to_char(_tick, _tfmt);
RAISE DEBUG 'Group for %, To: %, From: %', _grps, _grpname, _tbregex;
-- 获取所有符合命名规则的表，如无则退出
EXECUTE 'SELECT count(*) FROM pg_class WHERE relname ~ ' || quote_literal(_tbregex) INTO _tmp;
IF _tmp <> 0 THEN
RAISE DEBUG 'Group % sub-tables into %', _tmp, _grpname;
ELSE
RAISE DEBUG 'Sub-tables not found: %', quote_literal(_tbregex);
RETURN NULL;
END IF;
-- 检查现有复合表的子表关系，并将该子表和复合表的继承关系去除
www.2cto.com
EXECUTE 'select count(*) from pg_class where relname = ' || quote_literal(_grpname) INTO _tmp;
IF _tmp <> 0 THEN
OPEN _cursor FOR EXECUTE 'select a.relname from pg_catalog.pg_class a join pg_catalog.pg_inherits b on a.oid = b.inhrelid join pg_catalog.pg_class c on b.inhparent = c.oid where c.relname = ' || quote_literal(_grpname);
LOOP
FETCH _cursor INTO _subtbl;
IF NOT FOUND THEN EXIT; END IF;
RAISE DEBUG 'Cut table % from %', _subtbl, _grpname;
EXECUTE 'ALTER TABLE ' || _subtbl || ' NO INHERIT ' || _grpname;
END LOOP;
CLOSE _cursor;
-- 检查现有复合表，如有则删除
EXECUTE 'DROP TABLE IF EXISTS ' || _grpname;
END IF;
-- 建立复合表
EXECUTE 'CREATE TABLE ' || _grpname || '
(
id_moburlvisit BIGINT NOT NULL,
mob BIGINT NOT NULL,
lac INTEGER NOT NULL,
ci INTEGER NOT NULL,
id_urlrule INTEGER NOT NULL,
id_uarule INTEGER NOT NULL,
tick TIMESTAMP NOT NULL,
rate INTEGER NOT NULL,
pv INTEGER NOT NULL
)
WITH (OIDS=FALSE)';
-- 检查各子表，并将该子表添加进复合表 www.2cto.com
OPEN _cursor FOR EXECUTE 'SELECT relname FROM pg_class WHERE relname ~ ' || quote_literal(_tbregex);
LOOP
FETCH _cursor INTO _subtbl;
IF NOT FOUND THEN EXIT; END IF;
RAISE DEBUG 'Group table % into %', _subtbl, _grpname;
EXECUTE 'ALTER TABLE ' || _subtbl || ' INHERIT ' || _grpname;
END LOOP;
CLOSE _cursor;
RETURN _grpname;
END;
$$ LANGUAGE PLpgSQL;
其中查看了很多的相关博客，我也来总结一下PostgreSQL里面的字符串相关的函数吧！
函数：string || string
说明：String concatenation 字符串连接操作 www.2cto.com
例子：select 'Post' || 'greSQL'; ＝ PostgreS