Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0731552

Browse files
committed
add the partition name parameter to some PL functions
1 parentee432b0 commit0731552

File tree

1 file changed

+107
-60
lines changed

1 file changed

+107
-60
lines changed

‎range.sql

Lines changed: 107 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -396,7 +396,8 @@ $$ LANGUAGE plpgsql;
396396
CREATEOR REPLACE FUNCTION @extschema@.create_single_range_partition(
397397
parent_relidREGCLASS,
398398
p_start_valueANYELEMENT,
399-
p_end_valueANYELEMENT)
399+
p_end_valueANYELEMENT,
400+
partition_nameTEXT DEFAULTNULL)
400401
RETURNSTEXTAS
401402
$$
402403
DECLARE
@@ -423,34 +424,42 @@ BEGIN
423424

424425
v_seq_name := @extschema@.get_sequence_name(v_plain_schema, v_plain_relname);
425426

426-
/* Get next value from sequence*/
427-
LOOP
428-
v_part_num := nextval(v_seq_name);
429-
v_plain_child_relname := format('%s_%s', v_plain_relname, v_part_num);
430-
v_child_relname := format('%s.%s',
431-
quote_ident(v_plain_schema),
432-
quote_ident(v_plain_child_relname));
433-
434-
v_child_relname_exists :=count(*)>0
435-
FROM pg_class
436-
WHERE relname= v_plain_child_relnameAND
437-
relnamespace= v_plain_schema::regnamespace
438-
LIMIT1;
439-
440-
EXIT WHEN v_child_relname_exists= false;
441-
END LOOP;
427+
IF partition_name ISNULL THEN
428+
/* Get next value from sequence*/
429+
LOOP
430+
v_part_num := nextval(v_seq_name);
431+
v_plain_child_relname := format('%s_%s', v_plain_relname, v_part_num);
432+
v_child_relname := format('%s.%s',
433+
quote_ident(v_plain_schema),
434+
quote_ident(v_plain_child_relname));
435+
436+
v_child_relname_exists :=count(*)>0
437+
FROM pg_class
438+
WHERE relname= v_plain_child_relnameAND
439+
relnamespace= v_plain_schema::regnamespace
440+
LIMIT1;
441+
442+
EXIT WHEN v_child_relname_exists= false;
443+
END LOOP;
444+
ELSE
445+
v_child_relname := partition_name;
446+
END IF;
442447

443-
EXECUTE format('CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)',
444-
v_child_relname,
445-
@extschema@.get_schema_qualified_name(parent_relid));
446-
447-
EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',
448-
v_child_relname,
449-
@extschema@.build_check_constraint_name(v_child_relname::regclass,
450-
v_attname),
451-
@extschema@.build_range_condition(v_attname,
452-
p_start_value,
453-
p_end_value));
448+
EXECUTE format(
449+
'CREATE TABLE %1$s (LIKE %2$s INCLUDING ALL) INHERITS (%2$s)',
450+
v_child_relname,
451+
@extschema@.get_schema_qualified_name(parent_relid));
452+
453+
EXECUTE format(
454+
'ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s)',
455+
v_child_relname,
456+
@extschema@.build_check_constraint_name(
457+
v_child_relname::regclass,
458+
v_attname),
459+
@extschema@.build_range_condition(
460+
v_attname,
461+
p_start_value,
462+
p_end_value));
454463

455464
RETURN v_child_relname;
456465
END
@@ -463,6 +472,7 @@ SET client_min_messages = WARNING;
463472
CREATEOR REPLACE FUNCTION @extschema@.split_range_partition(
464473
p_partitionREGCLASS,
465474
p_valueANYELEMENT,
475+
partition_nameTEXT DEFAULTNULL,
466476
OUT p_rangeANYARRAY)
467477
RETURNS ANYARRAYAS
468478
$$
@@ -669,7 +679,8 @@ $$ LANGUAGE plpgsql;
669679
* Append new partition
670680
*/
671681
CREATEOR REPLACE FUNCTION @extschema@.append_range_partition(
672-
parent_relidREGCLASS)
682+
parent_relidREGCLASS,
683+
partition_nameTEXT DEFAULTNULL)
673684
RETURNSTEXTAS
674685
$$
675686
DECLARE
@@ -690,14 +701,20 @@ BEGIN
690701

691702
v_atttype := @extschema@.get_attribute_type_name(parent_relid, v_attname);
692703

693-
EXECUTE format('SELECT @extschema@.append_partition_internal($1, $2, $3, ARRAY[]::%s[])',
694-
v_atttype)
695-
INTO v_part_name
696-
USING parent_relid, v_atttype, v_interval;
704+
EXECUTE
705+
format(
706+
'SELECT @extschema@.append_partition_internal($1, $2, $3, ARRAY[]::%s[], $4)',
707+
v_atttype)
708+
USING
709+
parent_relid,
710+
v_atttype,
711+
v_interval,
712+
partition_name
713+
INTO
714+
v_part_name;
697715

698716
/* Invalidate cache*/
699717
PERFORM @extschema@.on_update_partitions(parent_relid);
700-
701718
RETURN v_part_name;
702719

703720
EXCEPTION WHEN others THEN
@@ -711,7 +728,8 @@ CREATE OR REPLACE FUNCTION @extschema@.append_partition_internal(
711728
parent_relidREGCLASS,
712729
p_atttypeTEXT,
713730
p_intervalTEXT,
714-
p_rangeANYARRAY DEFAULTNULL)
731+
p_rangeANYARRAY DEFAULTNULL,
732+
partition_nameTEXT DEFAULTNULL)
715733
RETURNSTEXTAS
716734
$$
717735
DECLARE
@@ -721,14 +739,23 @@ BEGIN
721739
p_range := @extschema@.get_range_by_idx(parent_relid,-1,0);
722740

723741
IF @extschema@.is_date_type(p_atttype::regtype) THEN
724-
v_part_name := @extschema@.create_single_range_partition(parent_relid
725-
, p_range[2]
726-
, p_range[2]+ p_interval::interval);
742+
v_part_name := @extschema@.create_single_range_partition(
743+
parent_relid,
744+
p_range[2],
745+
p_range[2]+ p_interval::interval,
746+
partition_name);
727747
ELSE
728-
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2, $2 + $3::%s)',
729-
p_atttype)
730-
USING parent_relid, p_range[2], p_interval
731-
INTO v_part_name;
748+
EXECUTE
749+
format(
750+
'SELECT @extschema@.create_single_range_partition($1, $2, $2 + $3::%s, $4)',
751+
p_atttype)
752+
USING
753+
parent_relid,
754+
p_range[2],
755+
p_interval,
756+
partition_name
757+
INTO
758+
v_part_name;
732759
END IF;
733760

734761
RETURN v_part_name;
@@ -741,7 +768,8 @@ LANGUAGE plpgsql;
741768
* Prepend new partition
742769
*/
743770
CREATEOR REPLACE FUNCTION @extschema@.prepend_range_partition(
744-
parent_relidREGCLASS)
771+
parent_relidREGCLASS,
772+
partition_nameTEXT DEFAULTNULL)
745773
RETURNSTEXTAS
746774
$$
747775
DECLARE
@@ -762,14 +790,20 @@ BEGIN
762790

763791
v_atttype := @extschema@.get_attribute_type_name(parent_relid, v_attname);
764792

765-
EXECUTE format('SELECT @extschema@.prepend_partition_internal($1, $2, $3, ARRAY[]::%s[])',
766-
v_atttype)
767-
INTO v_part_name
768-
USING parent_relid, v_atttype, v_interval;
793+
EXECUTE
794+
format(
795+
'SELECT @extschema@.prepend_partition_internal($1, $2, $3, ARRAY[]::%s[], $4)',
796+
v_atttype)
797+
USING
798+
parent_relid,
799+
v_atttype,
800+
v_interval,
801+
partition_name
802+
INTO
803+
v_part_name;
769804

770805
/* Invalidate cache*/
771806
PERFORM @extschema@.on_update_partitions(parent_relid);
772-
773807
RETURN v_part_name;
774808

775809
EXCEPTION WHEN others THEN
@@ -783,7 +817,8 @@ CREATE OR REPLACE FUNCTION @extschema@.prepend_partition_internal(
783817
parent_relidREGCLASS,
784818
p_atttypeTEXT,
785819
p_intervalTEXT,
786-
p_rangeANYARRAY DEFAULTNULL)
820+
p_rangeANYARRAY DEFAULTNULL,
821+
partition_nameTEXT DEFAULTNULL)
787822
RETURNSTEXTAS
788823
$$
789824
DECLARE
@@ -793,14 +828,23 @@ BEGIN
793828
p_range := @extschema@.get_range_by_idx(parent_relid,0,0);
794829

795830
IF @extschema@.is_date_type(p_atttype::regtype) THEN
796-
v_part_name := @extschema@.create_single_range_partition(parent_relid,
797-
p_range[1]- p_interval::interval,
798-
p_range[1]);
831+
v_part_name := @extschema@.create_single_range_partition(
832+
parent_relid,
833+
p_range[1]- p_interval::interval,
834+
p_range[1],
835+
partition_name);
799836
ELSE
800-
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2 - $3::%s, $2)',
801-
p_atttype)
802-
USING parent_relid, p_range[1], p_interval
803-
INTO v_part_name;
837+
EXECUTE
838+
format(
839+
'SELECT @extschema@.create_single_range_partition($1, $2 - $3::%s, $2, $4)',
840+
p_atttype)
841+
USING
842+
parent_relid,
843+
p_range[1],
844+
p_interval,
845+
partition_name
846+
INTO
847+
v_part_name;
804848
END IF;
805849

806850
RETURN v_part_name;
@@ -815,7 +859,8 @@ LANGUAGE plpgsql;
815859
CREATEOR REPLACE FUNCTION @extschema@.add_range_partition(
816860
parent_relidREGCLASS,
817861
p_start_valueANYELEMENT,
818-
p_end_valueANYELEMENT)
862+
p_end_valueANYELEMENT,
863+
partition_nameTEXT DEFAULTNULL)
819864
RETURNSTEXTAS
820865
$$
821866
DECLARE
@@ -832,9 +877,11 @@ BEGIN
832877
END IF;
833878

834879
/* Create new partition*/
835-
v_part_name := @extschema@.create_single_range_partition(parent_relid,
836-
p_start_value,
837-
p_end_value);
880+
v_part_name :=@extschema@.create_single_range_partition(
881+
parent_relid,
882+
p_start_value,
883+
p_end_value,
884+
partition_name);
838885
PERFORM @extschema@.on_update_partitions(parent_relid);
839886

840887
RETURN v_part_name;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp