一、概述:
PL/pgSQL函数在第一次被调用时,其函数内的源代码(文本)将被解析为二进制指令树,但是函数内的表达式和SQL命令只有在首次用到它们的时候,PL/pgSQL解释器才会为其创建一个准备好的执行规划,随后对该表达式或SQL命令的访问都将使用该规划。如果在一个条件语句中,有部分SQL命令或表达式没有被用到,那么PL/pgSQL解释器在本次调用中将不会为其准备执行规划,这样的好处是可以有效地减少为PL/pgSQL函数里的语句生成分析和执行规划的总时间,然而缺点是某些表达式或SQL命令中的错误只有在其被执行到的时候才能发现。
由于PL/pgSQL在函数里为一个命令制定了执行计划,那么在本次会话中该计划将会被反复使用,这样做往往可以得到更好的性能,但是如果你动态修改了相关的数据库对象,那么就有可能产生问题,如:
CREATE FUNCTION populate() RETURNS integer AS $$ DECLARE -- 声明段 BEGIN PERFORM my_function(); END; $$ LANGUAGE plpgsql;
鉴于以上规则,在PL/pgSQL里直接出现的SQL命令必须在每次执行时均引用相同的表和字段,换句话说,不能将函数的参数用作SQL命令的表名或字段名。如果想绕开该限制,可以考虑使用PL/pgSQL中的EXECUTE语句动态地构造命令,由此换来的代价是每次执行时都要构造一个新的命令计划。
使用PL/pgSQL函数的一个非常重要的优势是可以提高程序的执行效率,由于原有的SQL调用不得不在客户端与服务器之间反复传递数据,这样不仅增加了进程间通讯所产生的开销,而且也会大大增加网络IO的开销。
二、PL/pgSQL的结构:
PL/pgSQL是一种块结构语言,函数定义的所有文本都必须在一个块内,其中块中的每个声明和每条语句都是以分号结束,如果某一子块在另外一个块内,那么该子块的END关键字后面必须以分号结束,不过对于函数体的最后一个END关键字,分号可以省略,如:
[ <<label>> ] [ DECLARE declarations ] BEGIN statements END [ label ];
CREATE FUNCTION somefunc() RETURNS integer AS $$ DECLARE quantity integer := 30; BEGIN RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是30 quantity := 50; -- -- 创建一个子块 -- DECLARE quantity integer := 80; BEGIN RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是80 END; RAISE NOTICE 'Quantity here is %', quantity; --在这里的数量是50 RETURN quantity; END; $$ LANGUAGE plpgsql; #执行该函数以进一步观察其执行的结果。 postgres=# select somefunc(); NOTICE: Quantity here is 30 NOTICE: Quantity here is 80 NOTICE: Quantity here is 50 somefunc ---------- 50 (1 row)
三、声明:
所有在块里使用的变量都必须在块的声明段里先进行声明,唯一的例外是FOR循环里的循环计数变量,该变量被自动声明为整型。变量声明的语法如下:
variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
1. 函数参数的别名:
传递给函数的参数都是用$1、$2这样的标识符来表示的。为了增加可读性,我们可以为其声明别名。之后别名和数字标识符均可指向该参数值,见如下示例:
1). 在函数声明的同时给出参数变量名。
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$ DECLARE subtotal ALIAS FOR $1; BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ BEGIN tax := subtotal * 0.06; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; BEGIN result := v1 + v2 + v3; RETURN result; END; $$ LANGUAGE plpgsql;
variable%TYPE
user_id users.user_id%TYPE;
3. 行类型:
见如下形式的变量声明:
name table_name%ROWTYPE; name composite_type_name;
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$ DECLARE t2_row table2%ROWTYPE; BEGIN SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1; RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql;
name RECORD;
四、基本语句:
1. 赋值:
PL/pgSQL中赋值语句的形式为:identIFier := expression,等号两端的变量和表达式的类型或者一致,或者可以通过PostgreSQL的转换规则进行转换,否则将会导致运行时错误,见如下示例:
user_id := 20; tax := subtotal * 0.06;
SELECT INTO myrec * FROM emp WHERE empname = myname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', myname; END IF;
DECLARE users_rec RECORD; BEGIN SELECT INTO users_rec * FROM users WHERE user_id = 3; IF users_rec.homepage IS NULL THEN RETURN 'http://'; END IF; END;
PERFORM create_mv('cs_session_page_requests_mv', my_query);
和所有其它PL/pgSQL命令不同的是,一个由EXECUTE语句运行的命令在服务器内并不会只prepare和保存一次。相反,该语句在每次运行的时候,命令都会prepare一次。因此命令字符串可以在函数里动态的生成以便于对各种不同的表和字段进行操作,从而提高函数的灵活性。然而由此换来的却是性能上的折损。见如下示例:
EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);
五、控制结构:
1. 函数返回:
1). RETURN expression
该表达式用于终止当前的函数,然后再将expression的值返回给调用者。如果返回简单类型,那么可以使用任何表达式,同时表达式的类型也将被自动转换成函数的返回类型,就像我们在赋值中描述的那样。如果要返回一个复合类型的数值,则必须让表达式返回记录或者匹配的行变量。
2). RETURN NEXT expression
如果PL/pgSQL函数声明为返回SETOF sometype,其行记录是通过RETURN NEXT命令进行填充的,直到执行到不带参数的RETURN时才表示该函数结束。因此对于RETURN NEXT而言,它实际上并不从函数中返回,只是简单地把表达式的值保存起来,然后继续执行PL/pgSQL函数里的下一条语句。随着RETURN NEXT命令的迭代执行,结果集最终被建立起来。该类函数的调用方式如下:
SELECT * FROM some_func();
它被放在FROM子句中作为数据源使用。最后需要指出的是,如果结果集数量很大,那么通过该种方式来构建结果集将会导致极大的性能损失。
2. 条件:
在PL/pgSQL中有以下三种形式的条件语句。
1). IF-THEN
IF boolean-expression THEN statements END IF;
IF boolean-expression THEN statements ELSE statements END IF;
IF boolean-expression THEN statements ELSIF boolean-expression THEN statements ELSIF boolean-expression THEN statements ELSE statements END IF;
3. 循环:
1). LOOP
LOOP statements END LOOP [ label ];
EXIT [ label ] [ WHEN expression ];
LOOP -- do something EXIT WHEN count > 0; END LOOP;
CONTINUE [ label ] [ WHEN expression ];
LOOP -- do something EXIT WHEN count > 100; CONTINUE WHEN count < 50; END LOOP;
[ <<label>> ] WHILE expression LOOP statements END LOOP [ label ];
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP --do something END LOOP;
[ <<label>> ] FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP [ label ];
FOR i IN 1..10 LOOP --do something RAISE NOTICE 'i IS %', i; END LOOP; FOR i IN REVERSE 10..1 LOOP --do something END LOOP;
[ <<label>> ] FOR record_or_row IN query LOOP statements END LOOP [ label ];
FOR rec IN SELECT * FROM some_table LOOP PERFORM some_func(rec.one_col); END LOOP;
[ <<label>> ] FOR record_or_row IN EXECUTE text_expression LOOP statements END LOOP [ label ];
[ <<label>> ] [ DECLARE declarations ] BEGIN statements EXCEPTION WHEN condition [ OR condition ... ] THEN handler_statements WHEN condition [ OR condition ... ] THEN handler_statements END;
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones'); BEGIN UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones'; x := x + 1; y := x / 0; EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'caught division_by_zero'; RETURN x; END;
1. 声明游标变量:
在PL/pgSQL中对游标的访问都是通过游标变量实现的,其数据类型为refcursor。 创建游标变量的方法有以下两种:
1). 和声明其他类型的变量一样,直接声明一个游标类型的变量即可。
2). 使用游标专有的声明语法,如:
name CURSOR [ ( arguments ) ] FOR query;
curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
2. 打开游标:
游标在使用之前必须先被打开,在PL/pgSQL中有三种形式的OPEN语句,其中两种用于未绑定的游标变量,另外一种用于绑定的游标变量。
1). OPEN FOR:
其声明形式为:
OPEN unbound_cursor FOR query;
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound_cursor FOR EXECUTE query-string;
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
OPEN bound_cursor [ ( argument_values ) ];
OPEN curs2; OPEN curs3(42);
3. 使用游标:
游标一旦打开,就可以按照以下方式进行读取。然而需要说明的是,游标的打开和读取必须在同一个事物内,因为在PostgreSQL中,如果事物结束,事物内打开的游标将会被隐含的关闭。
1). FETCH
其声明形式为:
FETCH cursor INTO target;
FETCH curs1 INTO rowvar; --rowvar为行变量 FETCH curs2 INTO foo, bar, baz;
CLOSE cursor;
CLOSE curs1;
七、错误和消息:
在PostgreSQL中可以利用RAISE语句报告信息和抛出错误,其声明形式为:
RAISE level 'format' [, expression [, ...]];
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id; --v_job_id变量的值将替换format中的%。 RAISE EXCEPTION 'Inexistent ID --> %',user_id;