Here is the execution of the above function. (Subsequent WHEN expressions are not evaluated.) If no true result is found, the ELSE statements are executed but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.Ĭode: CREATE OR REPLACE FUNCTION myfunc1 (x integer) RETURNS text AS $$ Then the corresponding statements are executed, and then control passes to the next statement after END CASE. Each WHEN clause's boolean-expression is evaluated in turn until one is found that yields true. The searched form of CASE provides conditional execution based on the truth of Boolean expressions. WHEN department_id =100 THEN '2nd grade salary' (Subsequent WHEN expressions are not evaluated.) If no match is found, the ELSE statements are executed but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.ĬASE WHEN department_id =90 THEN 'High Salary' If a match is found, then the corresponding statements are executed, and then control passes to the next statement after END CASE. The search-expression is evaluated (once) and successively compared to each expression in the WHEN clauses. The simple form of CASE provides conditional execution based on equality of operands. Sample Output: postgres=# SELECT ret_date(CURRENT_DATE) Sample Output: postgres=# SELECT no_emp(8640,15) from employee Ĭode: CREATE FUNCTION ret_date (crdate date) RETURNS text AS $$ SELECT INTO no_emp count(*) FROM employee WHERE deptno=tot_dept SELECT INTO tmp_id mngr_no FROM employee WHERE Here is the sample table employee empno | emp_first_name | emp_last_name | designame | dt_birth | mngr_no | dt_join | salary | commission | deduction | deptno (Note this includes the case where the condition evaluates to NULL.)
IF-THEN-ELSE statements add to IF-THEN by letting you specify an alternative set of statements that should be executed if the condition is not true. To see the result from the function- postgres=# SELECT _ifstat1(current_date) Sample Output: postgres=# select ifstat() Ĭode: CREATE OR REPLACE FUNCTION ifstat (date) Otherwise, they are skipped.Ĭode: CREATE OR REPLACE FUNCTION ifstat() The statements between THEN and END IF will be executed if the condition is true. IF-THEN statements are the simplest form of IF. Here is the syntax of IF statements (three forms) : IF. IF and CASE are two conditionals statements and they are used under certain conditions. With PL/pgSQL's control structures, you can manipulate PostgreSQL data in a very flexible and powerful way.
In this section, we describe all the control structures statements, control structures are probably the most useful part of PL/pgSQL.
0 kommentar(er)
