基于开发者空间GaussDB云数据库的PLpgSQL实践二

[ <<标签>> ]
[ 声明
变量;
变量 := 赋值 ]
BEGINSQL QUERY
END [ 标签];

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

DECLAREcurs1 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);

DECLAREkey integer;curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGINkey := 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;

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

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

-- 此处自行创建用例表test
DROP TABLE IF EXISTS test;
CREATE TABLE test(col int);
CREATE OR REPLACE FUNCTION reffunc2() RETURNS refcursor AS $$
DECLAREref_temp refcursor;
BEGINOPEN 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();
FETCH ALL IN "<unnamed portal 3>";
COMMIT;

-- 此处简单创建用例表table_1, table_2
create table table_1(id int);
create table table_2(id int); 
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGINOPEN $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 [, ...] ) ] LOOPstatements
END LOOP [ label ];

CREATE or REPLACE FUNCTION get_score_point(vscore INT)
RETURNS DECIMAL
as $$
DECLARE
BEGINRETURN (CASEWHEN vscore <= 59 THEN0WHEN vscore <= 69 THEN0.1WHEN vscore <= 79 THEN0.2WHEN vscore <= 89 THEN0.3ELSE0.4END);
END; $$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION add_mask(id1 TEXT, id2 TEXT) RETURNS TEXT AS $$
DECLAREvar1 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;
BEGINopen 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 givenIF NEW.empname IS NULL THENRAISE EXCEPTION 'empname cannot be null';END IF;IF NEW.salary IS NULL THENRAISE EXCEPTION '% cannot have null salary', NEW.empname;END IF;-- Who works for us when they must pay for it?IF NEW.salary < 0 THENRAISE EXCEPTION '% cannot have a negative salary', NEW.empname;END IF;-- Remember who changed the payroll whenNEW.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 empFOR EACH ROW EXECUTE PROCEDURE 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') THENINSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;ELSIF (TG_OP = 'UPDATE') THENINSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;ELSIF (TG_OP = 'INSERT') THENINSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;END IF;RETURN NULL; -- result is ignored since this is an AFTER triggerEND;
$emp_audit$ LANGUAGE plpgsql;CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON empFOR EACH ROW EXECUTE PROCEDURE 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 ASSELECT e.empname,e.salary,max(ea.stamp) AS last_updatedFROM emp eLEFT JOIN emp_audit ea ON ea.empname = e.empnameGROUP 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') THENDELETE 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') THENUPDATE 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') THENINSERT 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_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_viewFOR EACH ROW EXECUTE PROCEDURE 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 TRIGGER
AS $maint_sales_summary_bytime$DECLAREdelta_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') THENdelta_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) THENRAISE 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') THENdelta_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>>LOOPUPDATE sales_summary_bytimeSET amount_sold = amount_sold + delta_amount_sold,units_sold = units_sold + delta_units_sold,amount_cost = amount_cost + delta_amount_costWHERE time_key = delta_time_key;EXIT insert_update WHEN found;BEGININSERT 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;EXCEPTIONWHEN UNIQUE_VIOLATION THEN-- do nothingEND;END LOOP insert_update;RETURN NULL;END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_factFOR EACH ROW EXECUTE PROCEDURE 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') THENINSERT INTO emp_auditSELECT 'D', now(), current_user, o.* FROM old_table o;ELSIF (TG_OP = 'UPDATE') THENINSERT INTO emp_auditSELECT 'U', now(), current_user, n.* FROM new_table n;ELSIF (TG_OP = 'INSERT') THENINSERT INTO emp_auditSELECT 'I', now(), current_user, n.* FROM new_table n;END IF;RETURN NULL; -- result is ignored since this is an AFTER triggerEND;
$emp_audit$ LANGUAGE plpgsql;CREATE TRIGGER emp_audit_insAFTER INSERT ON empREFERENCING NEW TABLE AS new_tableFOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();
CREATE TRIGGER emp_audit_updAFTER UPDATE ON empREFERENCING OLD TABLE AS old_table NEW TABLE AS new_tableFOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();
CREATE TRIGGER emp_audit_delAFTER DELETE ON empREFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

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

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

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，如果不是则显示为-- id2create or replace function add_mask(id1 text, id2 text)returns text as $$declarevar1 text;beginselect current_user into var1;if var1 = id1 thenreturn 'current user is ' || id1;elsereturn 'current user is ' || id2;end if;end;$$ language plpgsql;

RETURN expression;

-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;-- functions returning a composite type
RETURN 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$
DECLAREr foo%rowtype;
BEGINFOR r INSELECT * FROM foo WHERE fooid > 0LOOP-- can do some processing hereRETURN NEXT r; -- return current row of SELECTEND LOOP;RETURN;
END;
$BODY$
LANGUAGE plpgsql;SELECT * FROM get_all_foo();

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGINRETURN QUERY SELECT flightidFROM flightWHERE flightdate >= $1AND flightdate < ($1 + 1);-- Since execution is not finished, we can check whether rows were returned-- and raise exception if not.IF NOT FOUND THENRAISE 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 plpgsql
AS $$
BEGINFOR i IN 0..9 LOOPINSERT INTO test1 (a) VALUES (i);IF i % 2 = 0 THENCOMMIT;ELSEROLLBACK;END IF;END LOOP;
END;
$$;CALL transaction_test1();

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLAREr RECORD;
BEGINFOR r IN SELECT * FROM test2 ORDER BY x LOOPINSERT INTO test1 (a) VALUES (r.x);COMMIT;END LOOP;
END;
$$;CALL transaction_test2();

select * from score1 order by chinese limit 10
union 
select * from score2 order by chinese limit 10;select * from score1 order by chinese limit 10
union all
select * 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_tFROM "pg_database"WHERE datname not like '%template%' ) a,"pg_roles" bWHERE (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'