File: plpgsql.expect.txt

package info (click to toggle)
highlight.js 10.7.3%2Bdfsg-2
  • links: PTS, VCS
  • area: main
  • in suites: forky, sid
  • size: 8,332 kB
  • sloc: javascript: 41,059; makefile: 157; python: 29; sh: 20
file content (61 lines) | stat: -rw-r--r-- 5,752 bytes parent folder | download 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
<span class="hljs-comment">-- PL/pgSQL</span>

<span class="hljs-symbol">&lt;&lt; outerblock &gt;&gt;</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">&#x27;Quantity here is %&#x27;</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">&lt;&lt;outer_block&gt;&gt;</span>;
    <span class="hljs-keyword">ELSIF</span> quantity &lt; <span class="hljs-number">0</span> <span class="hljs-keyword">THEN</span>
        <span class="hljs-keyword">ASSERT</span> a &gt; b, <span class="hljs-string">&#x27;Bad luck&#x27;</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 &lt; <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">&#x27;SELECT count(*) FROM mytable WHERE inserted_by = $1&#x27;</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">&#x27;22012&#x27;</span> <span class="hljs-keyword">THEN</span>
        <span class="hljs-keyword">NULL</span>;
<span class="hljs-keyword">END</span>;