관리-도구
편집 파일: plpgsql.expect.txt
<span class="hljs-comment">-- PL/pgSQL</span> <span class="hljs-symbol"><< outerblock >></span> <span class="hljs-keyword">DECLARE</span> quantity <span class="hljs-type">integer</span> := <span class="hljs-number">30</span>; subtotal <span class="hljs-keyword">ALIAS</span> <span class="hljs-keyword">FOR</span> <span class="hljs-meta">$1</span>; prior <span class="hljs-keyword">ALIAS</span> <span class="hljs-keyword">FOR</span> <span class="hljs-built_in">old</span>; arow <span class="hljs-type">record</span>; curs1 <span class="hljs-type">refcursor</span>; curs2 <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> tenk1; <span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">DECLARE</span> quantity <span class="hljs-keyword">CONSTANT</span> <span class="hljs-type">integer</span> := <span class="hljs-number">80</span>; myrow tablename<span class="hljs-meta">%ROWTYPE</span>; myfield tablename.columnname<span class="hljs-meta">%TYPE</span>; <span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">PERFORM</span> pg_sleep(<span class="hljs-number">1</span>); <span class="hljs-keyword">RAISE</span> <span class="hljs-keyword">NOTICE</span> <span class="hljs-string">'Quantity here is %'</span>, quantity; <span class="hljs-keyword">END</span>; <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">INTO</span> myrec <span class="hljs-keyword">FROM</span> emp <span class="hljs-keyword">WHERE</span> empname = myname; <span class="hljs-keyword">IF</span> <span class="hljs-keyword">NOT</span> <span class="hljs-built_in">FOUND</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">EXIT</span> <span class="hljs-symbol"><<outer_block>></span>; <span class="hljs-keyword">ELSIF</span> quantity < <span class="hljs-number">0</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">ASSERT</span> a > b, <span class="hljs-string">'Bad luck'</span>; <span class="hljs-keyword">END</span> <span class="hljs-keyword">IF</span>; <span class="hljs-keyword">FOR</span> r <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> foo <span class="hljs-keyword">LOOP</span> <span class="hljs-keyword">CONTINUE</span> <span class="hljs-keyword">WHEN</span> count < <span class="hljs-number">50</span>; <span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>; <span class="hljs-keyword">FOR</span> i <span class="hljs-keyword">IN</span> <span class="hljs-keyword">REVERSE</span> <span class="hljs-number">10.</span><span class="hljs-number">.1</span> <span class="hljs-keyword">LOOP</span> <span class="hljs-keyword">FOREACH</span> x <span class="hljs-keyword">IN</span> <span class="hljs-keyword">ARRAY</span> <span class="hljs-meta">$1</span> <span class="hljs-keyword">LOOP</span> s := s + x; <span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>; <span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>; <span class="hljs-keyword">WHILE</span> <span class="hljs-keyword">NOT</span> done <span class="hljs-keyword">LOOP</span> <span class="hljs-keyword">CASE</span> x <span class="hljs-keyword">WHEN</span> <span class="hljs-number">1</span>, <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">RETURN NEXT</span> r; <span class="hljs-keyword">ELSE</span> <span class="hljs-keyword">RETURN QUERY</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sales; <span class="hljs-keyword">END</span> <span class="hljs-keyword">CASE</span>; <span class="hljs-keyword">END</span> <span class="hljs-keyword">LOOP</span>; <span class="hljs-keyword">EXECUTE</span> <span class="hljs-string">'SELECT count(*) FROM mytable WHERE inserted_by = $1'</span> <span class="hljs-keyword">INTO</span> c <span class="hljs-keyword">USING</span> checked_user; <span class="hljs-keyword">OPEN</span> curs1 <span class="hljs-keyword">SCROLL FOR</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> foo <span class="hljs-keyword">WHERE</span> key = mykey; <span class="hljs-keyword">FETCH LAST</span> <span class="hljs-keyword">FROM</span> curs1 <span class="hljs-keyword">INTO</span> x, y; <span class="hljs-keyword">MOVE RELATIVE</span> <span class="hljs-number">-2</span> <span class="hljs-keyword">FROM</span> curs1; <span class="hljs-keyword">UPDATE</span> foo <span class="hljs-keyword">SET</span> dataval = myval <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">OF</span> curs1; <span class="hljs-keyword">CLOSE</span> curs1; <span class="hljs-keyword">RETURN</span> quantity; <span class="hljs-keyword">EXCEPTION</span> <span class="hljs-keyword">WHEN</span> <span class="hljs-built_in">NO_DATA_FOUND</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">GET</span> <span class="hljs-keyword">DIAGNOSTICS</span> integer_var = <span class="hljs-built_in">ROW_COUNT</span>; <span class="hljs-keyword">WHEN</span> <span class="hljs-built_in">SQLSTATE</span> <span class="hljs-string">'22012'</span> <span class="hljs-keyword">THEN</span> <span class="hljs-keyword">NULL</span>; <span class="hljs-keyword">END</span>;