procedure_plpgsql

# LogicTest: !local-mixed-23.1

statement ok
CREATE TABLE t (
  k INT PRIMARY KEY,
  i INT,
  s STRING
)

statement ok
CREATE PROCEDURE my_upsert(arg_k INT, new_i INT, new_s STRING) AS $$
  DECLARE
    c INT;
  BEGIN
    SELECT count(*) INTO c FROM t WHERE k = arg_k;
    IF c > 0 THEN
      UPDATE t SET i = new_i, s = new_s WHERE k = arg_k;
    ELSE
      INSERT INTO t VALUES (arg_k, new_i, new_s);
    END IF;
  END
$$ LANGUAGE PLpgSQL

statement ok
CALL my_upsert(1, 10, 'foo')

statement ok
CALL my_upsert(2, 20, 'bar')

query IIT rowsort
SELECT * FROM t
----
1  10  foo
2  20  bar

statement ok
CALL my_upsert(1, 100, 'baz')

query IIT rowsort
SELECT * FROM t
----
1  100  baz
2  20   bar

subtest composite_into

# Regression test for #114683 - if the target of a SELECT INTO statement is a
# single composite-typed variable, the columns will be wrapped with a tuple,
# which will be assigned to the variable.
statement ok
CREATE TABLE t114683 (x INT, y INT);
INSERT INTO t114683 (SELECT t, t%6 FROM generate_series(1, 13) g(t));

statement ok
CREATE OR REPLACE PROCEDURE get_rows(n INT) LANGUAGE PLpgSQL AS $$
  DECLARE
    v t114683;
    count INT;
    i INT := 0;
  BEGIN
    count := (SELECT count(*) FROM t114683);
    WHILE i < count LOOP
      IF n = 0 THEN
        SELECT x, y INTO v FROM t114683 ORDER BY y, x OFFSET i;
      ELSIF n = 1 THEN
        SELECT ROW(x, y) INTO v FROM t114683 ORDER BY y, x OFFSET i;
      ELSIF n = 2 THEN
        SELECT ROW(x, y) INTO v FROM t114683 ORDER BY y, x OFFSET i;
        RAISE NOTICE 'v: %', v::TEXT::t114683;
      ELSIF n = 3 THEN
        SELECT x, y, x+y INTO v FROM t114683 ORDER BY y, x OFFSET i;
      ELSE
        SELECT x INTO v FROM t114683 ORDER BY y, x OFFSET i;
      END IF;
      RAISE NOTICE 'v: %', v;
      i := i + 1;
    END LOOP;
  END
$$;

query T noticetrace
CALL get_rows(0);
----
NOTICE: v: (6,0)
NOTICE: v: (12,0)
NOTICE: v: (1,1)
NOTICE: v: (7,1)
NOTICE: v: (13,1)
NOTICE: v: (2,2)
NOTICE: v: (8,2)
NOTICE: v: (3,3)
NOTICE: v: (9,3)
NOTICE: v: (4,4)
NOTICE: v: (10,4)
NOTICE: v: (5,5)
NOTICE: v: (11,5)

statement error pgcode 22P02 could not parse
CALL get_rows(1);

# Casting to text and then to "t114683" shows the error that should occur in
# the previous test case.
statement error pgcode 22P02 could not parse
CALL get_rows(2);

# The number of columns exceeds the length of the INTO variable.
query T noticetrace
CALL get_rows(3);
----
NOTICE: v: (6,0)
NOTICE: v: (12,0)
NOTICE: v: (1,1)
NOTICE: v: (7,1)
NOTICE: v: (13,1)
NOTICE: v: (2,2)
NOTICE: v: (8,2)
NOTICE: v: (3,3)
NOTICE: v: (9,3)
NOTICE: v: (4,4)
NOTICE: v: (10,4)
NOTICE: v: (5,5)
NOTICE: v: (11,5)

# The number of columns is less than the length of the INTO variable.
query T noticetrace
CALL get_rows(4);
----
NOTICE: v: (6,)
NOTICE: v: (12,)
NOTICE: v: (1,)
NOTICE: v: (7,)
NOTICE: v: (13,)
NOTICE: v: (2,)
NOTICE: v: (8,)
NOTICE: v: (3,)
NOTICE: v: (9,)
NOTICE: v: (4,)
NOTICE: v: (10,)
NOTICE: v: (5,)
NOTICE: v: (11,)

# The target of a FETCH statement has the same behavior as above.
statement ok
CREATE OR REPLACE PROCEDURE get_rows(n INT) LANGUAGE PLpgSQL AS $$
  DECLARE
    curs REFCURSOR;
    v t114683;
  BEGIN
    IF n = 0 THEN
      OPEN curs FOR SELECT 1, 2;
    ELSIF n = 1 THEN
      OPEN curs FOR SELECT ROW(1, 2);
    ELSIF n = 2 THEN
      OPEN curs FOR SELECT 1, 2, 3;
    ELSE
      OPEN curs FOR SELECT 1;
    END IF;
    FETCH curs INTO v;
    RAISE NOTICE '%', v;
  END
$$;

query T noticetrace
CALL get_rows(0);
----
NOTICE: (1,2)

statement error pgcode 42846 invalid cast
CALL get_rows(1);

query T noticetrace
CALL get_rows(2);
----
NOTICE: (1,2)

query T noticetrace
CALL get_rows(3);
----
NOTICE: (1,)

subtest exit_continue_cond

statement ok
CREATE PROCEDURE p(a INT, b INT, c INT) AS $$
  DECLARE
    i INT := 0;
  BEGIN
    LOOP
      RAISE NOTICE 'iteration %', i;
      i := i + 1;
      CONTINUE WHEN i = a;
      RAISE NOTICE 'new value of i: %', i;
      EXIT WHEN i >= b;
      CONTINUE WHEN i <> c;
      RAISE NOTICE 'returning';
      RETURN;
    END LOOP;
    RAISE NOTICE 'exited loop';
  END
$$ LANGUAGE PLpgSQL;

query T noticetrace
CALL p(2, 1, 4);
----
NOTICE: iteration 0
NOTICE: new value of i: 1
NOTICE: exited loop

query T noticetrace
CALL p(2, 3, 4);
----
NOTICE: iteration 0
NOTICE: new value of i: 1
NOTICE: iteration 1
NOTICE: iteration 2
NOTICE: new value of i: 3
NOTICE: exited loop

query T noticetrace
CALL p(2, 4, 4);
----
NOTICE: iteration 0
NOTICE: new value of i: 1
NOTICE: iteration 1
NOTICE: iteration 2
NOTICE: new value of i: 3
NOTICE: iteration 3
NOTICE: new value of i: 4
NOTICE: exited loop

query T noticetrace
CALL p(2, 5, 4);
----
NOTICE: iteration 0
NOTICE: new value of i: 1
NOTICE: iteration 1
NOTICE: iteration 2
NOTICE: new value of i: 3
NOTICE: iteration 3
NOTICE: new value of i: 4
NOTICE: returning

subtest exit_continue_label

# EXIT can apply to a block with a label.
statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() AS $$
  BEGIN
    <<foo>>
    BEGIN
      RAISE NOTICE 'before EXIT';
      EXIT foo;
      RAISE NOTICE 'after EXIT';
    END;
  END
$$ LANGUAGE PLpgSQL;

query T noticetrace
CALL p();
----
NOTICE: before EXIT

statement ok
DROP PROCEDURE p;

# EXIT without a label cannot apply to a block.
statement error pgcode 42601 pq: EXIT cannot be used outside a loop, unless it has a label
CREATE PROCEDURE p() AS $$
  BEGIN
    <<foo>>
    BEGIN
      RAISE NOTICE 'before EXIT';
      EXIT;
      RAISE NOTICE 'after EXIT';
    END;
  END
$$ LANGUAGE PLpgSQL;

# CONTINUE cannot apply to a block, with or without a label.
statement error pgcode 42601 pq: CONTINUE cannot be used outside a loop
CREATE PROCEDURE p() AS $$
  BEGIN
    <<foo>>
    BEGIN
      RAISE NOTICE 'before EXIT';
      CONTINUE foo;
      RAISE NOTICE 'after EXIT';
    END;
  END
$$ LANGUAGE PLpgSQL;

# CONTINUE the inner loop.
statement ok
CREATE PROCEDURE p(x INT) AS $$
  <<b1>>
  DECLARE
    i INT := 0;
  BEGIN
    RAISE NOTICE '>> b1 %', i;
    <<l1>>
    WHILE i < 2 LOOP
      i := i + 1;
      RAISE NOTICE '>> l1 %', i;
      <<b2>>
      DECLARE
        j int := 0;
      BEGIN
        RAISE NOTICE '>> b2 % %', i, j;
        <<l2>>
        WHILE j < i LOOP
          j := j + 1;
          RAISE NOTICE '>> l2 % %', i, j;
          IF x = 0 THEN
            IF j = 1 THEN RAISE NOTICE 'CONTINUE l2'; END IF;
            CONTINUE l2 WHEN j = 1;
          ELSIF x = 1 THEN
            IF j = 1 THEN RAISE NOTICE 'EXIT l2'; END IF;
            EXIT l2 WHEN j = 1;
          ELSIF x = 2 THEN
            IF j = 1 THEN RAISE NOTICE 'CONTINUE l1'; END IF;
            CONTINUE l1 WHEN j = 1;
          ELSIF x = 3 THEN
            IF j = 1 THEN RAISE NOTICE 'EXIT l1'; END IF;
            EXIT l1 WHEN j = 1;
          ELSIF x = 4 THEN
            IF j = 1 THEN RAISE NOTICE 'EXIT b2'; END IF;
            EXIT b2 WHEN j = 1;
          -- TODO(drewk): uncomment these branches when it's possible to
          -- reference the root block.
          -- ELSIF x = 5 THEN
          --   EXIT b1 WHEN j = 1;
          -- ELSE
          --   EXIT p WHEN j = 1;
          END IF;
          RAISE NOTICE '<< l2 % %', i, j;
        END LOOP l2;
        RAISE NOTICE '<< b2 % %', i, j;
      END;
      RAISE NOTICE '<< l1 %', i;
    END LOOP l1;
    RAISE NOTICE '<< b1 %', i;
  END;
$$ LANGUAGE PLpgSQL;

# CONTINUE inner loop.
query T noticetrace
CALL p(0);
----
NOTICE: >> b1 0
NOTICE: >> l1 1
NOTICE: >> b2 1 0
NOTICE: >> l2 1 1
NOTICE: CONTINUE l2
NOTICE: << b2 1 1
NOTICE: << l1 1
NOTICE: >> l1 2
NOTICE: >> b2 2 0
NOTICE: >> l2 2 1
NOTICE: CONTINUE l2
NOTICE: >> l2 2 2
NOTICE: << l2 2 2
NOTICE: << b2 2 2
NOTICE: << l1 2
NOTICE: << b1 2

# EXIT inner loop.
query T noticetrace
CALL p(1);
----
NOTICE: >> b1 0
NOTICE: >> l1 1
NOTICE: >> b2 1 0
NOTICE: >> l2 1 1
NOTICE: EXIT l2
NOTICE: << b2 1 1
NOTICE: << l1 1
NOTICE: >> l1 2
NOTICE: >> b2 2 0
NOTICE: >> l2 2 1
NOTICE: EXIT l2
NOTICE: << b2 2 1
NOTICE: << l1 2
NOTICE: << b1 2

# CONTINUE outer loop.
query T noticetrace
CALL p(2);
----
NOTICE: >> b1 0
NOTICE: >> l1 1
NOTICE: >> b2 1 0
NOTICE: >> l2 1 1
NOTICE: CONTINUE l1
NOTICE: >> l1 2
NOTICE: >> b2 2 0
NOTICE: >> l2 2 1
NOTICE: CONTINUE l1
NOTICE: << b1 2

# EXIT outer loop.
query T noticetrace
CALL p(3);
----
NOTICE: >> b1 0
NOTICE: >> l1 1
NOTICE: >> b2 1 0
NOTICE: >> l2 1 1
NOTICE: EXIT l1
NOTICE: << b1 1

# EXIT inner block.
query T noticetrace
CALL p(4);
----
NOTICE: >> b1 0
NOTICE: >> l1 1
NOTICE: >> b2 1 0
NOTICE: >> l2 1 1
NOTICE: EXIT b2
NOTICE: << l1 1
NOTICE: >> l1 2
NOTICE: >> b2 2 0
NOTICE: >> l2 2 1
NOTICE: EXIT b2
NOTICE: << l1 2
NOTICE: << b1 2

statement ok
DROP PROCEDURE p;
CREATE PROCEDURE p() AS $$
  DECLARE
    i INT := 0;
  BEGIN
    <<l1>>
    LOOP
      i := i + 1;
      EXIT l1 WHEN i >= 5;
      DECLARE
        j int := 0;
      BEGIN
        <<l2>>
        LOOP
          j := j + 1;
          RAISE NOTICE '% %', i, j;
          CONTINUE l1 WHEN j >= i;
        END LOOP l2;
      END;
    END LOOP l1;
  END;
$$ LANGUAGE PLpgSQL;

query T noticetrace
CALL p();
----
NOTICE: 1 1
NOTICE: 2 1
NOTICE: 2 2
NOTICE: 3 1
NOTICE: 3 2
NOTICE: 3 3
NOTICE: 4 1
NOTICE: 4 2
NOTICE: 4 3
NOTICE: 4 4

subtest end