DROP FUNCTION
说明:
移除一个函数
语法:
DROP FUNCTION [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
[ CASCADE | RESTRICT ]
示例:
--移除sqrt对应的两个函数
DROP FUNCTION sqrt(integer), sqrt(bigint);
CREATE PROCEDURE
说明:
定义一个新的过程
语法:
CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
示例:
--创建一个插入数据的过程,用CALL调用:
CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
CALL insert_data(1, 2);
ALTER PROCEDURE
说明:
更改一个过程的定义
语法:
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
action [ ... ] [ RESTRICT ]
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
RENAME TO new_name
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
SET SCHEMA new_schema
ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
DEPENDS ON EXTENSION extension_name
其中action是下列之一:
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
SET configuration_parameter { TO | = } { value | DEFAULT }
SET configuration_parameter FROM CURRENT
RESET configuration_parameter
RESET ALL
示例:
--重命名具有两个integer类型参数的过程insert_data为insert_record:
ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
--要把具有两个integer类型参数的过程insert_data的拥有者改为joe:
ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
DROP PROCEDURE
说明:
移除一个过程
语法:
DROP PROCEDURE [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
[ CASCADE | RESTRICT ]
示例:
--移除过程insert_data:
DROP PROCEDURE insert_data();
CREATE SCHEMA
说明:
定义一个新模式
语法:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
其中 role_specification 可以是:
user_name
| CURRENT_USER
| SESSION_USER
示例:
--创建一个模式:
CREATE SCHEMA myschema;
--为用户joe创建一个模式,该模式也将被命名为 joe:
CREATE SCHEMA AUTHORIZATION joe;
ALTER SCHEMA
说明:
更改一个模式的定义
语法:
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
示例:
--将模式sch_a重命名为sch_b:
ALTER SCHEMA sch_a RENAME TO sch_b;