create or replace function wyy_createRangePartition(tableName varchar)
returns void as $$
declare
v_cmd varchar(255);
v_thisMonth date;
v_nextMonth date;
begin
for i in 0..59 loop
v_thisMonth:=wyy.EXTRACT_DATE(wyy_util.ADD_MONTHS(now(),i)); -- wyy.EXTRACT_DATE返回类型为date
v_nextMonth:=wyy.EXTRACT_DATE(wyy_util.ADD_MONTHS(now(),i+1));
v_cmd := 'create table P_' || tableName || '_'||date_part('year',v_thisMonth)||date_part('month',v_thisMonth) ||
' (CHECK ( SUBMITTIME >= ' || v_thisMonth || ' AND SUBMITTIME < ' || v_nextMonth ||' ))INHERITS (' || tableName || ' );';
execute v_cmd;
end loop;
end;
$$ LANGUAGE plpgsql;create table test(submittime date);
select wyy_createRangePartition('test');
错误: 操作符不存在: date >= integer
提示: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
背景: SQL 语句 "create table P_test_20114 (CHECK ( SUBMITTIME >= 2011-04-19 AND SUBMITTIME < 2011-05-19 ))INHERITS (test );"可是我的 v_thisMonth和v_nextMonth本来就是date类型呀。不明白为什么有这个错,也不知道该如何改,请大家指教,谢谢
returns void as $$
declare
v_cmd varchar(255);
v_thisMonth date;
v_nextMonth date;
begin
for i in 0..59 loop
v_thisMonth:=wyy.EXTRACT_DATE(wyy_util.ADD_MONTHS(now(),i)); -- wyy.EXTRACT_DATE返回类型为date
v_nextMonth:=wyy.EXTRACT_DATE(wyy_util.ADD_MONTHS(now(),i+1));
v_cmd := 'create table P_' || tableName || '_'||date_part('year',v_thisMonth)||date_part('month',v_thisMonth) ||
' (CHECK ( SUBMITTIME >= ' || v_thisMonth || ' AND SUBMITTIME < ' || v_nextMonth ||' ))INHERITS (' || tableName || ' );';
execute v_cmd;
end loop;
end;
$$ LANGUAGE plpgsql;create table test(submittime date);
select wyy_createRangePartition('test');
错误: 操作符不存在: date >= integer
提示: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
背景: SQL 语句 "create table P_test_20114 (CHECK ( SUBMITTIME >= 2011-04-19 AND SUBMITTIME < 2011-05-19 ))INHERITS (test );"可是我的 v_thisMonth和v_nextMonth本来就是date类型呀。不明白为什么有这个错,也不知道该如何改,请大家指教,谢谢
所以必须
'SUBMITTIME >= cast(' || v_thisMonth || ' as DATE) ....'
还必须是
'SUBMITTIME >= cast($$' || v_thisMonth || '$$ as DATE) ....'
为此我修改了代码如下:
v_thisMonth varchar(10);
v_nextMonth varchar(10);
begin
for i in 0..59 loop
v_thisMonth:=to_char(wyy.ADD_MONTHS(now(),i),'YYYYMMDD'); --转为纯数字的字符类型,因为日期类型中有‘-’,在整形转数字时会出错。
v_nextMonth:=to_char(wyy.ADD_MONTHS(now(),i+1),'YYYYMMDD');
v_cmd := 'create table P_' || tableName || '_'||substr(v_thisMonth,1,6) ||
' (CHECK ( SUBMITTIME >= cast(cast(' || v_thisMonth || ' as varchar)as DATE) AND SUBMITTIME < cast(cast(' || v_nextMonth || ' as varchar)as date) ))INHERITS (' || tableName || ' );';总之,谢谢你了