基于开发者空间 OpenGauss 数据库的 PLPGSQL 实践二

cd $GAUSSHOME/bin

./gs_initdb -D data --nodename=n1 -w GaussDB@123

./gs_ctl start -D data -Z single_node -l logfile

./gsql -d postgres -ar

[ <<标签>> ][ 声明变量;变量 := 赋值 ]BEGIN    SQL QUERYEND [ 标签];

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

DECLARE    curs1 refcursor;    curs2 CURSOR FOR SELECT * FROM tenk1;    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string [ USING expression [, ... ] ];

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

OPEN curs2;OPEN curs3(42);OPEN curs3(key := 42);

DECLARE    key integer;    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;BEGIN    key := 42;    OPEN curs4;

FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCH curs1 INTO rowvar;FETCH curs2 INTO foo, bar, baz;FETCH LAST FROM curs3 INTO x, y;FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE [ direction { FROM | IN } ] cursor;

MOVE curs1;MOVE LAST FROM curs3;MOVE RELATIVE -2 FROM curs4;MOVE FORWARD 2 FROM curs4;

UPDATE table SET ... WHERE CURRENT OF cursor;DELETE FROM table WHERE CURRENT OF cursor;

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE cursor;

CLOSE curs1;

DORP TABLE IF EXISTS test;CREATE TABLE test (col text);INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS 'BEGIN    OPEN $1 FOR SELECT col FROM test;    RETURN $1;END;' LANGUAGE plpgsql;

BEGIN;SELECT reffunc('funccursor');FETCH ALL IN funccursor;COMMIT;

-- 此处自行创建用例表testDROP TABLE IF EXISTS test;CREATE TABLE test(col int);CREATE FUNCTION reffunc2() RETURNS refcursor AS $$DECLARE    ref_temp refcursor;BEGIN    OPEN ref_temp FOR SELECT col FROM test;    RETURN ref_temp;END;$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.BEGIN;SELECT reffunc2();
      reffunc2-------------------- <unnamed cursor 1>(1 row)
FETCH ALL IN "<unnamed cursor 1>";COMMIT;

-- 此处简单创建用例表table_1, table_2create table table_1(id int);create table table_2(id int); CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$BEGIN    OPEN $1 FOR SELECT * FROM table_1;    RETURN NEXT $1;    OPEN $2 FOR SELECT * FROM table_2;    RETURN NEXT $2;END;$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;FETCH ALL FROM b;COMMIT;

[ <<label>> ]FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP    statementsEND LOOP [ label ];

CREATE or REPLACE FUNCTION get_score_point(vscore INT)RETURNS DECIMALas $$DECLAREBEGIN  RETURN (  CASE    WHEN vscore <= 59 THEN      0    WHEN vscore <= 69 THEN      0.1    WHEN vscore <= 79 THEN      0.2    WHEN vscore <= 89 THEN      0.3    ELSE      0.4  END);END; $$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION add_mask(id1 TEXT, id2 TEXT) RETURNS TEXT AS $$DECLARE    var1 TEXT;    var2 int;    cursor c1 is select case when id::text = id2 then '***' else id::text end as id, ranking from (select id, dense_rank() over(order by sum_point desc) as ranking from (select id,get_score_point(math)+get_score_point(art)+get_score_point(phy) as sum_point from stu)) where id::text = id1;BEGIN    open c1;    loop fetch c1 into var1, var2;    exit when c1%notfound;        return dbe_output.print_line('学号是：'||var1||', 排名是：'||var2);    end loop;    close c1;END;$$ LANGUAGE PLPGSQL;

TRIGGER

CREATE TABLE emp (    empname           text,    salary            integer,    last_date         timestamp,    last_user         text);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$    BEGIN        -- Check that empname and salary are given        IF NEW.empname IS NULL THEN            RAISE EXCEPTION 'empname cannot be null';        END IF;        IF NEW.salary IS NULL THEN            RAISE EXCEPTION '% cannot have null salary', NEW.empname;        END IF;
        -- Who works for us when they must pay for it?        IF NEW.salary < 0 THEN            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;        END IF;
        -- Remember who changed the payroll when        NEW.last_date := current_timestamp;        NEW.last_user := current_user;        RETURN NEW;    END;$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

CREATE TABLE emp (    empname           text NOT NULL,    salary            integer);
CREATE TABLE emp_audit(    operation         char(1)   NOT NULL,    stamp             timestamp NOT NULL,    userid            text      NOT NULL,    empname           text      NOT NULL,    salary            integer);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$    BEGIN        --        -- Create a row in emp_audit to reflect the operation performed on emp,        -- making use of the special variable TG_OP to work out the operation.        --        IF (TG_OP = 'DELETE') THEN            INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;        ELSIF (TG_OP = 'UPDATE') THEN            INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;        ELSIF (TG_OP = 'INSERT') THEN            INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;        END IF;        RETURN NULL; -- result is ignored since this is an AFTER trigger    END;$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_auditAFTER INSERT OR UPDATE OR DELETE ON emp    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

CREATE TABLE emp (    empname           text PRIMARY KEY,    salary            integer);
CREATE TABLE emp_audit(    operation         char(1)   NOT NULL,    userid            text      NOT NULL,    empname           text      NOT NULL,    salary            integer,    stamp             timestamp NOT NULL);
CREATE VIEW emp_view AS    SELECT e.empname,           e.salary,           max(ea.stamp) AS last_updated      FROM emp e      LEFT JOIN emp_audit ea ON ea.empname = e.empname     GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$    BEGIN        --        -- Perform the required operation on emp, and create a row in emp_audit        -- to reflect the change made to emp.        --        IF (TG_OP = 'DELETE') THEN            DELETE FROM emp WHERE empname = OLD.empname;            IF NOT FOUND THEN RETURN NULL; END IF;
            OLD.last_updated = now();            INSERT INTO emp_audit VALUES('D', current_user, OLD.*);            RETURN OLD;        ELSIF (TG_OP = 'UPDATE') THEN            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;            IF NOT FOUND THEN RETURN NULL; END IF;
            NEW.last_updated = now();            INSERT INTO emp_audit VALUES('U', current_user, NEW.*);            RETURN NEW;        ELSIF (TG_OP = 'INSERT') THEN            INSERT INTO emp VALUES(NEW.empname, NEW.salary);
            NEW.last_updated = now();            INSERT INTO emp_audit VALUES('I', current_user, NEW.*);            RETURN NEW;        END IF;    END;$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_auditINSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view    FOR EACH ROW EXECUTE FUNCTION update_emp_view();

---- Main tables - time dimension and sales fact.--CREATE TABLE time_dimension (    time_key                    integer NOT NULL,    day_of_week                 integer NOT NULL,    day_of_month                integer NOT NULL,    month                       integer NOT NULL,    quarter                     integer NOT NULL,    year                        integer NOT NULL);CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (    time_key                    integer NOT NULL,    product_key                 integer NOT NULL,    store_key                   integer NOT NULL,    amount_sold                 numeric(12,2) NOT NULL,    units_sold                  integer NOT NULL,    amount_cost                 numeric(12,2) NOT NULL);CREATE INDEX sales_fact_time ON sales_fact(time_key);
---- Summary table - sales by time.--CREATE TABLE sales_summary_bytime (    time_key                    integer NOT NULL,    amount_sold                 numeric(15,2) NOT NULL,    units_sold                  numeric(12) NOT NULL,    amount_cost                 numeric(15,2) NOT NULL);CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
---- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.--CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGERAS $maint_sales_summary_bytime$    DECLARE        delta_time_key          integer;        delta_amount_sold       numeric(15,2);        delta_units_sold        numeric(12);        delta_amount_cost       numeric(15,2);    BEGIN
        -- Work out the increment/decrement amount(s).        IF (TG_OP = 'DELETE') THEN
            delta_time_key = OLD.time_key;            delta_amount_sold = -1 * OLD.amount_sold;            delta_units_sold = -1 * OLD.units_sold;            delta_amount_cost = -1 * OLD.amount_cost;
        ELSIF (TG_OP = 'UPDATE') THEN
            -- forbid updates that change the time_key -            -- (probably not too onerous, as DELETE + INSERT is how most            -- changes will be made).            IF ( OLD.time_key != NEW.time_key) THEN                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',                                                      OLD.time_key, NEW.time_key;            END IF;
            delta_time_key = OLD.time_key;            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;            delta_units_sold = NEW.units_sold - OLD.units_sold;            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
        ELSIF (TG_OP = 'INSERT') THEN
            delta_time_key = NEW.time_key;            delta_amount_sold = NEW.amount_sold;            delta_units_sold = NEW.units_sold;            delta_amount_cost = NEW.amount_cost;
        END IF;

        -- Insert or update the summary row with the new values.        <<insert_update>>        LOOP            UPDATE sales_summary_bytime                SET amount_sold = amount_sold + delta_amount_sold,                    units_sold = units_sold + delta_units_sold,                    amount_cost = amount_cost + delta_amount_cost                WHERE time_key = delta_time_key;
            EXIT insert_update WHEN found;
            BEGIN                INSERT INTO sales_summary_bytime (                            time_key,                            amount_sold,                            units_sold,                            amount_cost)                    VALUES (                            delta_time_key,                            delta_amount_sold,                            delta_units_sold,                            delta_amount_cost                           );
                EXIT insert_update;
            EXCEPTION                WHEN UNIQUE_VIOLATION THEN                    -- do nothing            END;        END LOOP insert_update;
        RETURN NULL;
    END;$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytimeAFTER INSERT OR UPDATE OR DELETE ON sales_fact    FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();INSERT INTO sales_fact VALUES(1,1,1,10,3,15);INSERT INTO sales_fact VALUES(1,2,1,20,5,35);INSERT INTO sales_fact VALUES(2,2,1,40,15,135);INSERT INTO sales_fact VALUES(2,3,1,10,1,13);SELECT * FROM sales_summary_bytime;DELETE FROM sales_fact WHERE product_key = 1;SELECT * FROM sales_summary_bytime;UPDATE sales_fact SET units_sold = units_sold * 2;SELECT * FROM sales_summary_bytime;

CREATE TABLE emp (    empname           text NOT NULL,    salary            integer);
CREATE TABLE emp_audit(    operation         char(1)   NOT NULL,    stamp             timestamp NOT NULL,    userid            text      NOT NULL,    empname           text      NOT NULL,    salary            integer);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$    BEGIN        --        -- Create rows in emp_audit to reflect the operations performed on emp,        -- making use of the special variable TG_OP to work out the operation.        --        IF (TG_OP = 'DELETE') THEN            INSERT INTO emp_audit                SELECT 'D', now(), current_user, o.* FROM old_table o;        ELSIF (TG_OP = 'UPDATE') THEN            INSERT INTO emp_audit                SELECT 'U', now(), current_user, n.* FROM new_table n;        ELSIF (TG_OP = 'INSERT') THEN            INSERT INTO emp_audit                SELECT 'I', now(), current_user, n.* FROM new_table n;        END IF;        RETURN NULL; -- result is ignored since this is an AFTER trigger    END;$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit_ins    AFTER INSERT ON emp    REFERENCING NEW TABLE AS new_table    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();CREATE TRIGGER emp_audit_upd    AFTER UPDATE ON emp    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();CREATE TRIGGER emp_audit_del    AFTER DELETE ON emp    REFERENCING OLD TABLE AS old_tableFOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$BEGIN    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;END;$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();

CREATE PROCEDURE triple(INOUT x int)LANGUAGE plpgsqlAS $$BEGIN    x := x * 3;END;$$;
DO $$DECLARE myvar int := 5;BEGIN  CALL triple(myvar);  RAISE NOTICE 'myvar = %', myvar;  -- prints 15END;$$;

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];RAISE [ level ] condition_name [ USING option = expression [, ... ] ];RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];RAISE [ level ] USING option = expression [, ... ];RAISE ;

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

-- 编写add_mask(id1,id2)函数，当id1是当前查询用户时，显示正常ID，如果不是则显示为-- id2    create or replace function add_mask(id1 text, id2 text)    returns text as $$    declare        var1 text;    begin        select current_user into var1;        if var1 = id1 then            return 'current user is ' || id1;        else            return 'current user is ' || id2;        end if;    end;    $$ language plpgsql;

RETURN expression;

-- functions returning a scalar typeRETURN 1 + 2;RETURN scalar_var;
-- functions returning a composite typeRETURN composite_type_var;RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

RETURN NEXT expression;RETURN QUERY query;RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);INSERT INTO foo VALUES (1, 2, 'three');INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS$BODY$DECLARE    r foo%rowtype;BEGIN    FOR r IN        SELECT * FROM foo WHERE fooid > 0    LOOP        -- can do some processing here        RETURN NEXT r; -- return current row of SELECT    END LOOP;    RETURN;END;$BODY$LANGUAGE plpgsql;
SELECT * FROM get_all_foo();

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS$BODY$BEGIN    RETURN QUERY SELECT flightid                   FROM flight                  WHERE flightdate >= $1                    AND flightdate < ($1 + 1);
    -- Since execution is not finished, we can check whether rows were returned    -- and raise exception if not.    IF NOT FOUND THEN        RAISE EXCEPTION 'No flight at %.', $1;    END IF;
    RETURN; END;$BODY$LANGUAGE plpgsql;
-- Returns available flights or raises exception if there are no-- available flights.SELECT * FROM get_available_flightid(CURRENT_DATE);

CREATE PROCEDURE transaction_test1()LANGUAGE plpgsqlAS $$BEGIN    FOR i IN 0..9 LOOP        INSERT INTO test1 (a) VALUES (i);        IF i % 2 = 0 THEN            COMMIT;        ELSE            ROLLBACK;        END IF;    END LOOP;END;$$;
CALL transaction_test1();

CREATE PROCEDURE transaction_test2()LANGUAGE plpgsqlAS $$DECLARE    r RECORD;BEGIN    FOR r IN SELECT * FROM test2 ORDER BY x LOOP        INSERT INTO test1 (a) VALUES (r.x);        COMMIT;    END LOOP;END;$$;
CALL transaction_test2();

select * from score1 order by chinese limit 10union select * from score2 order by chinese limit 10;
select * from score1 order by chinese limit 10union allselect * from score2 order by chinese limit 10;

dense_rank() over([partition by column] order by column desc)

CREATE TABLE dense_rank_t1(a int, b int);INSERT INTO dense_rank_t1 VALUES(1,1),(1,1),(1, 2),(1, 3),(2, 4),(2, 5),(3,6);SELECT a,b,DENSE_RANK() OVER(PARTITION BY a ORDER BY b) FROM dense_rank_t1;

select chinese from score where chinese not in (select chinese from score2);

MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id)       WHEN MATCHED THEN          UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'      WHEN NOT MATCHED THEN          INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';

-- family_name跟first_name用'.'拼接，要求首字母大写select id, initcap(family_name||'.'||first_name), sexmark, grade from su;

select t1.*,rolname from (select datname,(aclexplode(datacl)).grantee, (aclexplode(datacl)).privilege_type from pg_database) t1,pg_roles where grantee=pg_roles.oid and rolname='sjh111' and datname not like '%template%';-- 查看用户user1和数据库的相关权限，题目提示用pg_database和pg_roles,要求显示数据库名、用户名、数据库的权限(一定要背下来，原题，而且不要去格式美化)SELECT a.datname, b.rolname, string_agg(a.priv_t, ',')  from (SELECT datname, (aclexplode(COALESCE(datacl, acldefault('d' :: "char", datdba)))).grantee as grantee, (aclexplode(COALESCE(datacl, acldefault('d' :: "char", datdba)))).privilege_type as priv_t          FROM "pg_database"         WHERE datname not like '%template%' ) a,       "pg_roles" b WHERE (a.grantee = 0 or a.grantee = b.oid)   AND b.rolname = 'user1' GROUP BY a.datname, b.rolname;

INSERT INTO xxx ON DUPLICATE KEY UPDATE expression

insert into su values(2,'tom','jerry','tom','H',63) on duplicate key update mark='F'