PostgreSQL plpgsql返回record实例.docVIP

PostgreSQL plpgsql返回record实例 --下面是返回一个ROW类型的例子，可以用RECORD类型和%ROWTYPE类型来付值 CREATE table ret_row ( bh varchar(10), xm varchar(20), xb VARCHAR(2) ) ; INSERT INTO ret_row VALUES(001,阿弟,男); INSERT INTO ret_row VALUES(002,吕不为,男); INSERT INTO ret_row VALUES(003,有理想的猪,男); CREATE OR REPLACE FUNCTION ret_row() RETURNS SETOF ret_row AS $$ DECLARE v_rec ret_row%rowtype; v_rec_record RECORD; BEGIN FOR v_rec IN select * from ret_row LOOP RETURN NEXT v_rec; END LOOP; FOR v_rec_record IN select * from ret_row LOOP RETURN NEXT v_rec_record; END LOOP; RETURN ; END; $$ LANGUAGE PLPGSQL; SELECT * FROM ret_row(); --下面是用自定义TYPE返回一个RECORD，可以用RECORD类型和%ROWTYPE类型来付值 CREATE TYPE ret_type AS ( bh VARCHAR, xm VARCHAR ); CREATE OR REPLACE FUNCTION ret_type() RETURNS SETOF ret_type AS $$ DECLARE v_rec ret_type; v_rec_record RECORD; BEGIN FOR v_rec IN SELECT bh,xm FROM ret_row LOOP RETURN NEXT v_rec; END LOOP; FOR v_rec_record IN SELECT bh,xm FROM ret_row LOOP RETURN NEXT v_rec_record; END LOOP; RETURN ; END; $$ LANGUAGE PLPGSQL; SELECT * FROM ret_type() --利用OUT返回 CREATE OR REPLACE FUNCTION ret_out(OUT a_bh varchar,OUT a_xm VARCHAR) RETURNS SETOF RECORD AS $$ DECLARE v_rec_record RECORD; BEGIN FOR v_rec_record IN SELECT bh,xm FROM ret_row LOOP a_bh:=v_rec_record.bh; a_xm:=v_rec_record.xm; RETURN NEXT; END LOOP; END; $$ LANGUAGE PLPGSQL; SELECT * FROM ret_out(); --直接record返回 CREATE OR REPLACE FUNCTION ret_record() RETURNS SETOF RECORD AS $$ DECLARE v_rec RECORD; BEGIN FOR v_rec IN SELECT bh,xm FROM ret_row LOOP RETURN NEXT v_rec; END LOOP; RETURN; END; $$ LANGUAGE PLPGSQL; SELECT * FROM ret_record() t(bh VARCHAR,xm VARCHAR); ??????????????????????????????????????????