postgresql plpgsql返回record实例

《postgresql plpgsql返回record实例》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、PostgreSQLplpgsql返回record实例--下面是返回一个ROW类型的例子，可以用RECORD类型和%ROWTYPE类型来付值CREATEtableret_row(bhvarchar(10),xmvarchar(20),xbVARCHAR(2));INSERTINTOret_rowVALUES('001','阿弟','男');INSERTINTOret_rowVALUES('002','吕不为','男');INSERTINTOret_rowVALUES('003','有理想的猪','男');CREATEORREPLACEFUNCTIONret_row()RETURN

2、SSETOFret_rowAS$$DECLAREv_recret_row%rowtype;v_rec_recordRECORD;BEGINFORv_recINselect*fromret_rowLOOPRETURNNEXTv_rec;ENDLOOP;FORv_rec_recordINselect*fromret_rowLOOPRETURNNEXTv_rec_record;ENDLOOP;RETURN;END;$$LANGUAGEPLPGSQL;SELECT*FROMret_row();--下面是用自定义TYPE返回一个RECORD，可以用RECORD类型和%ROWTYPE类型来付

3、值CREATETYPEret_typeAS(bhVARCHAR,xmVARCHAR);CREATEORREPLACEFUNCTIONret_type()RETURNSSETOFret_typeAS$$DECLAREv_recret_type;v_rec_recordRECORD;BEGINFORv_recINSELECTbh,xmFROMret_rowLOOPRETURNNEXTv_rec;ENDLOOP;FORv_rec_recordINSELECTbh,xmFROMret_rowLOOPRETURNNEXTv_rec_record;ENDLOOP;RETURN;END;$$L

4、ANGUAGEPLPGSQL;SELECT*FROMret_type()--利用OUT返回CREATEORREPLACEFUNCTIONret_out(OUTa_bhvarchar,OUTa_xmVARCHAR)RETURNSSETOFRECORDAS$$DECLAREv_rec_recordRECORD;BEGINFORv_rec_recordINSELECTbh,xmFROMret_rowLOOPa_bh:=v_rec_record.bh;a_xm:=v_rec_record.xm;RETURNNEXT;ENDLOOP;END;$$LANGUAGEPLPGSQL;SELECT

5、*FROMret_out();--直接record返回CREATEORREPLACEFUNCTIONret_record()RETURNSSETOFRECORDAS$$DECLAREv_recRECORD;BEGINFORv_recINSELECTbh,xmFROMret_rowLOOPRETURNNEXTv_rec;ENDLOOP;RETURN;END;$$LANGUAGEPLPGSQL;SELECT*FROMret_record()t(bhVARCHAR,xmVARCHAR);