PostgreSQL stored procedure returns result set

Recently because the work needs to write a login check function, words not much to say, paste code, because it is just a simple introduction to use, so the core judgment I have all removed.

The first requirement is to return only one user ID, so a simple returns integer can be completed.

CREATE OR REPLACE FUNCTION Validate_user () RETURNS integer  as $BODY $ DECLARE integer ; begin return i; End  ;

--Changed the requirements later, asked to return the value of a row in the table, so I used the cursor, but after the value of the tour cursor is too cumbersome, so modified again.

CREATE OR REPLACE FUNCTIONValidate_user ()RETURNSRefcursor as$BODY $Declareuser_list refcursor;begin OpenUser_list for Select *  fromTb_userwhereEmail=Usernamete andState=1;returnuser_list;End; $BODY $language plpgsql volatilecost -;

The following is the standard notation for Pg/sql

CREATE OR REPLACE FUNCTIONValidate_user ()RETURNSSetof Tb_user as$BODY $Declareuser_list Tb_user;begin  forUser_listinch Select *  fromTb_userwhereEmail=Usernamete andUserpwd=Passwordte andState=1Loopreturn Nextuser_list;EndLoop;End; $BODY $language plpgsql volatilecost -; 

There is also a direct select, which is not posted here, after the success of the decision to directly use the SELECT statement on the line, no return value.

We need to add a parameter back to the judging situation, so I use the out output here, is also more flexible. The output parameter prefix plus T is because the PSQL version variable I'm using is not conflicting.

--output parameter usage

CREATE OR REPLACE FUNCTIONValidate_user (Usernametetext, Passwordtetext, Out Tuseridint, Out Tpasswordtext, Out Tusernametext, Out Tcompidint, Out Troleint, Out Terrcodeint) as$BODY $begin SelectUserid,userpwd,username,compid,role,0  fromTb_userwhereEmail=Usernamete andState=1  into$3,$4,$5,$6,$7,$8;return;End; $BODY $language plpgsql volatilecost -;

PostgreSQL stored procedure returns result set