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