GreenPlum建库、建表、增删改查、函数、物理化视图、规则、外表、存储过程等

-------------如果帮助到你了，请点个赞，一键三连是小编的最爱------------------

create DATABASE mm;
CREATE schema xx;
SELECT * from yy
-----创建表测试
CREATE table my_table(col1 INT,col2 INT,col3 INT);
-----------创建规则
CREATE RULE my_rule AS ON INSERT TO my_table
DO INSTEAD
INSERT INTO my_table(col1, col2, col3)
VALUES (NEW.col1, NEW.col2 * 2, NEW.col3);
----------使用Alter修规规则的名字----------------
ALTER RULE my_rule ON my_table RENAME TO new_rule;
ALTER RULE wwy01_rule_name ON wwy01 RENAME TO wwy01_rule_name_01;

--创建物化视图
CREATE MATERIALIZED VIEW   materialized_view_name2  AS SELECT col1,col2,col3 FROM  my_table;

ALTER MATERIALIZED VIEW materialized_view_name2 REFRESH FAST; 
-- 修改物化视图名称
ALTER MATERIALIZED VIEW materialized_view_name2 RENAME TO new_materialized_view;

-- 使用CREATE_RULE   创建规则，需要先建立转换关系：
--首先，假设我们有一个名为 "sales" 的表，其中包含销售数据的列 "product_id"、"quantity" 和 "price"：
CREATE TABLE sales (
    product_id INT,
    quantity INT,
    price NUMERIC
);
--现在，我们想要在向 "sales" 表插入数据时自动计算总销售额，并将结果存储到另一个表 "sales_summary" 中。我们可以使用 CREATE RULE 来实现这个功能。
--首先，创建 "sales_summary" 表：
CREATE TABLE sales_summary (
    total_sales NUMERIC
);
--然后，创建规则来定义插入数据时的转换操作：
CREATE RULE calculate_sales_summary AS
    ON INSERT TO sales
    DO ALSO
    (
        INSERT INTO sales_summary (total_sales)
        SELECT SUM(quantity * price)
        FROM sales;
    );

INSERT INTO sales (product_id, quantity, price)
VALUES (1, 10, 10), (2, 5, 20), (3, 3, 15);

SELECT total_sales
FROM sales_summary;
--使用alter rule
--创建规则后使用Alter  rule修改规则
ALTER RULE calculate_total_sales
    ON SELECT TO sales
    WHERE quantity > 0
    DO INSTEAD
    (
        SELECT *,
               quantity * price AS total_sales
        FROM sales;
    );

--delete——table 删除
delete from "xx"."yy" where "AA" = '1'::TEXT and "B" = '岩下劲松'::TEXT and "C" = '少年气'::TEXT and "D" = '婉兮清扬'::TEXT and "E" = '白昼流星'::TEXT and "F" = '来路可期'::TEXT AND ctid in (select ctid from "xx"."yy" where "AA" = '1'::TEXT and "B" = '岩下劲松'::TEXT and "C" = '少年气'::TEXT and "D" = '婉兮清扬'::TEXT and "E" = '白昼流星'::TEXT and "F" = '来路可期'::TEXT order by ctid limit 1)
--update进行编辑
update "xx"."yy" set "AA" = '1111'::TEXT where "AA" = '1'::TEXT and "B" = '岩下劲松'::TEXT and "C" = '少年气'::TEXT and "D" = '婉兮清扬'::TEXT and "E" = '白昼流星'::TEXT and "F" = '来路可期'::TEXT AND ctid in (select ctid from "xx"."yy" where "AA" = '1'::TEXT and "B" = '岩下劲松'::TEXT and "C" = '少年气'::TEXT and "D" = '婉兮清扬'::TEXT and "E" = '白昼流星'::TEXT and "F" = '来路可期'::TEXT order by ctid limit 1)
--重新插入数据
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('2'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('3'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('4'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('5'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('6'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('7'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('8'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('9'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('10'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('11'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("AA","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
--查询表，限制查询1000条，从第0行开始查询
SELECT * FROM "mm"."xx"."yy" LIMIT 1000 OFFSET 0
--表重命名
ALTER TABLE yy1 RENAME TO yy;
--列重命名
ALTER TABLE yy RENAME COLUMN "A" TO "AA";
--取消非空列的约束
ALTER TABLE yy ALTER COLUMN "A" DROP NOT NULL;
--设置列为非空
ALTER TABLE yy ALTER COLUMN "A" SET NOT NULL;
--给列添加唯一约束
--先确定没有重复的字段--我去还必须是带着主键
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (user_id);

ALTER TABLE yy ADD CONSTRAINT "aaaa"  UNIQUE ("A");

ALTER TABLE "mm"."xx"."yy" ADD CONSTRAINT unique_username UNIQUE ('B'::TEXT);

CREATE UNIQUE INDEX "aaaa" ON yy ('A'::TEXT);
--删除表  --cascades删除任何引用地方
ALTER TABLE yy DROP COLUMN description CASCADE;
--修改表 --添加列
ALTER TABLE yy ADD COLUMN description text CHECK (description <> '');
--创建存储过程
drop table if exists "mm"."xx"."accounts";
create table "mm"."xx"."accounts" (
    id int,
    name varchar(100) not null,
    balance dec(15, 2) not null
);
  
insert into "mm"."xx"."accounts"(id,name, balance)values(1,'Raju', 10000);
insert into "mm"."xx"."accounts"(id ,name, balance)values(2,'Nikhil', 10000);

CREATE OR REPLACE PROCEDURE FUNCTION "mm"."xx"."transfer"(
   sender int,
   receiver int,
   amount dec
)
RETURNS void
language plpgsql  
as $ FUNCTION $
begin
    update accounts
    set balance = balance - amount
    where id = sender;
    update accounts
    set balance = balance + amount
    where id = receiver;
  
    commit;
end;$ FUNCTION $;
--创建函数
create or replace function Zbc(var integer)
returns integer as '
begin
    var := var+1;
    return var;   
end; '
language plpgsql;
--创建物化视图
CREATE MATERIALIZED VIEW Drfed AS
SELECT *
FROM "mm"."xx"."yy"
--创建视图
CREATE VIEW csdf AS
SELECT *
FROM "mm"."xx"."yy"
--创建普通表
CREATE TABLE IF NOT EXISTS "mm"."xx"."aasc"
( "A" text ,
 "B" text ,
 "C" text ,
 "D" text ,
 "E" text ,
 "F" text
);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('2'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('3'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('4'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('5'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('6'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('7'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('8'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('9'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('10'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('11'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
INSERT INTO "xx"."yy" ("A","B","C","D","E","F") VALUES ('1'::text,'岩下劲松'::text,'少年气'::text,'婉兮清扬'::text,'白昼流星'::text,'来路可期'::text);
select * from xx.yy;
--创建外表
CREATE EXTERNAL TABLE "mm"."xx"."my_ext_table" (
    id INTEGER,
    name VARCHAR(50),
    age INTEGER
) ;
CREATE RULE wwy01_rule_name AS ON UPDATE TO yy DO INSTEAD (
-- Rule action goes here
);