--
-- CREATE_FUNCTION_SQL
--
-- Assorted tests using SQL-language functions
--
-- All objects made in this test are in temp_func_test schema
CREATE USER regress_unpriv_user;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE SCHEMA temp_func_test;
GRANT ALL ON SCHEMA temp_func_test TO public;
SET search_path TO temp_func_test, public;
--
-- Make sanity checks on the pg_proc entries created by CREATE FUNCTION
--
--
-- ARGUMENT and RETURN TYPES
--
CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
       AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
       AS 'SELECT $1[1]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
       AS 'SELECT false';
SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
       WHERE oid in ('functest_A_1'::regproc,
                     'functest_A_2'::regproc,
                     'functest_A_3'::regproc) ORDER BY proname;
   proname    | prorettype |    proargtypes    
--------------+------------+-------------------
 functest_a_1 | boolean    | [0:1]={text,date}
 functest_a_2 | integer    | [0:0]={text[]}
 functest_a_3 | boolean    | {}
(3 rows)

SELECT functest_A_1('abcd', '2020-01-01');
 functest_a_1 
--------------
 t
(1 row)

SELECT functest_A_2(ARRAY['1', '2', '3']);
 functest_a_2 
--------------
            1
(1 row)

SELECT functest_A_3();
 functest_a_3 
--------------
 f
(1 row)

--
-- IMMUTABLE | STABLE | VOLATILE
--
CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql'
       AS 'SELECT $1 > 0';
CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql'
       IMMUTABLE AS 'SELECT $1 > 0';
CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql'
       STABLE AS 'SELECT $1 = 0';
CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql'
       VOLATILE AS 'SELECT $1 < 0';
SELECT proname, provolatile FROM pg_proc
       WHERE oid in ('functest_B_1'::regproc,
                     'functest_B_2'::regproc,
                     'functest_B_3'::regproc,
		     'functest_B_4'::regproc) ORDER BY proname;
   proname    | provolatile 
--------------+-------------
 functest_b_1 | v
 functest_b_2 | i
 functest_b_3 | s
 functest_b_4 | v
(4 rows)

ALTER FUNCTION functest_B_2(int) VOLATILE;
ALTER FUNCTION functest_B_3(int) COST 100;	-- unrelated change, no effect
SELECT proname, provolatile FROM pg_proc
       WHERE oid in ('functest_B_1'::regproc,
                     'functest_B_2'::regproc,
                     'functest_B_3'::regproc,
		     'functest_B_4'::regproc) ORDER BY proname;
   proname    | provolatile 
--------------+-------------
 functest_b_1 | v
 functest_b_2 | v
 functest_b_3 | s
 functest_b_4 | v
(4 rows)

--
-- SECURITY DEFINER | INVOKER
--
CREATE FUNCTION functest_C_1(int) RETURNS bool LANGUAGE 'sql'
       AS 'SELECT $1 > 0';
CREATE FUNCTION functest_C_2(int) RETURNS bool LANGUAGE 'sql'
       SECURITY DEFINER AS 'SELECT $1 = 0';
CREATE FUNCTION functest_C_3(int) RETURNS bool LANGUAGE 'sql'
       SECURITY INVOKER AS 'SELECT $1 < 0';
SELECT proname, prosecdef FROM pg_proc
       WHERE oid in ('functest_C_1'::regproc,
                     'functest_C_2'::regproc,
                     'functest_C_3'::regproc) ORDER BY proname;
   proname    | prosecdef 
--------------+-----------
 functest_c_1 | f
 functest_c_2 | t
 functest_c_3 | f
(3 rows)

ALTER FUNCTION functest_C_1(int) IMMUTABLE;	-- unrelated change, no effect
ALTER FUNCTION functest_C_2(int) SECURITY INVOKER;
ALTER FUNCTION functest_C_3(int) SECURITY DEFINER;
SELECT proname, prosecdef FROM pg_proc
       WHERE oid in ('functest_C_1'::regproc,
                     'functest_C_2'::regproc,
                     'functest_C_3'::regproc) ORDER BY proname;
   proname    | prosecdef 
--------------+-----------
 functest_c_1 | f
 functest_c_2 | f
 functest_c_3 | t
(3 rows)

--
-- LEAKPROOF
--
CREATE FUNCTION functest_E_1(int) RETURNS bool LANGUAGE 'sql'
       AS 'SELECT $1 > 100';
CREATE FUNCTION functest_E_2(int) RETURNS bool LANGUAGE 'sql'
       LEAKPROOF AS 'SELECT $1 > 100';
SELECT proname, proleakproof FROM pg_proc
       WHERE oid in ('functest_E_1'::regproc,
                     'functest_E_2'::regproc) ORDER BY proname;
   proname    | proleakproof 
--------------+--------------
 functest_e_1 | f
 functest_e_2 | t
(2 rows)

ALTER FUNCTION functest_E_1(int) LEAKPROOF;
ALTER FUNCTION functest_E_2(int) STABLE;	-- unrelated change, no effect
SELECT proname, proleakproof FROM pg_proc
       WHERE oid in ('functest_E_1'::regproc,
                     'functest_E_2'::regproc) ORDER BY proname;
   proname    | proleakproof 
--------------+--------------
 functest_e_1 | t
 functest_e_2 | t
(2 rows)

ALTER FUNCTION functest_E_2(int) NOT LEAKPROOF;	-- remove leakproof attribute
SELECT proname, proleakproof FROM pg_proc
       WHERE oid in ('functest_E_1'::regproc,
                     'functest_E_2'::regproc) ORDER BY proname;
   proname    | proleakproof 
--------------+--------------
 functest_e_1 | t
 functest_e_2 | f
(2 rows)

-- it takes superuser privilege to turn on leakproof, but not to turn off
ALTER FUNCTION functest_E_1(int) OWNER TO regress_unpriv_user;
ALTER FUNCTION functest_E_2(int) OWNER TO regress_unpriv_user;
SET SESSION AUTHORIZATION regress_unpriv_user;
SET search_path TO temp_func_test, public;
ALTER FUNCTION functest_E_1(int) NOT LEAKPROOF;
ALTER FUNCTION functest_E_2(int) LEAKPROOF;
ERROR:  only superuser or mdb_admin can define a leakproof function
CREATE FUNCTION functest_E_3(int) RETURNS bool LANGUAGE 'sql'
       LEAKPROOF AS 'SELECT $1 < 200';	-- fail
ERROR:  only superuser or mdb_admin can define a leakproof function
RESET SESSION AUTHORIZATION;
--
-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--
CREATE FUNCTION functest_F_1(int) RETURNS bool LANGUAGE 'sql'
       AS 'SELECT $1 > 50';
CREATE FUNCTION functest_F_2(int) RETURNS bool LANGUAGE 'sql'
       CALLED ON NULL INPUT AS 'SELECT $1 = 50';
CREATE FUNCTION functest_F_3(int) RETURNS bool LANGUAGE 'sql'
       RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
CREATE FUNCTION functest_F_4(int) RETURNS bool LANGUAGE 'sql'
       STRICT AS 'SELECT $1 = 50';
SELECT proname, proisstrict FROM pg_proc
       WHERE oid in ('functest_F_1'::regproc,
                     'functest_F_2'::regproc,
                     'functest_F_3'::regproc,
                     'functest_F_4'::regproc) ORDER BY proname;
   proname    | proisstrict 
--------------+-------------
 functest_f_1 | f
 functest_f_2 | f
 functest_f_3 | t
 functest_f_4 | t
(4 rows)

ALTER FUNCTION functest_F_1(int) IMMUTABLE;	-- unrelated change, no effect
ALTER FUNCTION functest_F_2(int) STRICT;
ALTER FUNCTION functest_F_3(int) CALLED ON NULL INPUT;
SELECT proname, proisstrict FROM pg_proc
       WHERE oid in ('functest_F_1'::regproc,
                     'functest_F_2'::regproc,
                     'functest_F_3'::regproc,
                     'functest_F_4'::regproc) ORDER BY proname;
   proname    | proisstrict 
--------------+-------------
 functest_f_1 | f
 functest_f_2 | t
 functest_f_3 | f
 functest_f_4 | t
(4 rows)

-- pg_get_functiondef tests
SELECT pg_get_functiondef('functest_A_1'::regproc);
                         pg_get_functiondef                         
--------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_a_1(text, date)+
  RETURNS boolean                                                  +
  LANGUAGE sql                                                     +
 AS $function$SELECT $1 = 'abcd' AND $2 > '2001-01-01'$function$   +
 
(1 row)

SELECT pg_get_functiondef('functest_B_3'::regproc);
                       pg_get_functiondef                        
-----------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_b_3(integer)+
  RETURNS boolean                                               +
  LANGUAGE sql                                                  +
  STABLE                                                        +
 AS $function$SELECT $1 = 0$function$                           +
 
(1 row)

SELECT pg_get_functiondef('functest_C_3'::regproc);
                       pg_get_functiondef                        
-----------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_c_3(integer)+
  RETURNS boolean                                               +
  LANGUAGE sql                                                  +
  SECURITY DEFINER                                              +
 AS $function$SELECT $1 < 0$function$                           +
 
(1 row)

SELECT pg_get_functiondef('functest_F_2'::regproc);
                       pg_get_functiondef                        
-----------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_f_2(integer)+
  RETURNS boolean                                               +
  LANGUAGE sql                                                  +
  STRICT                                                        +
 AS $function$SELECT $1 = 50$function$                          +
 
(1 row)

--
-- SQL-standard body
--
CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean
    LANGUAGE SQL
    RETURN a = 'abcd' AND b > '2001-01-01';
CREATE FUNCTION functest_S_2(a text[]) RETURNS int
    RETURN a[1]::int;
CREATE FUNCTION functest_S_3() RETURNS boolean
    RETURN false;
CREATE FUNCTION functest_S_3a() RETURNS boolean
    BEGIN ATOMIC
        ;;RETURN false;;
    END;
CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean
    LANGUAGE SQL
    BEGIN ATOMIC
        SELECT a = 'abcd' AND b > '2001-01-01';
    END;
CREATE FUNCTION functest_S_13() RETURNS boolean
    BEGIN ATOMIC
        SELECT 1;
        SELECT false;
    END;
-- check display of function arguments in sub-SELECT
CREATE TABLE functest1 (i int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE FUNCTION functest_S_16(a int, b int) RETURNS void
    LANGUAGE SQL
    BEGIN ATOMIC
        INSERT INTO functest1 SELECT a + $2;
    END;
-- error: duplicate function body
CREATE FUNCTION functest_S_xxx(x int) RETURNS int
    LANGUAGE SQL
    AS $$ SELECT x * 2 $$
    RETURN x * 3;
ERROR:  duplicate function body specified
-- polymorphic arguments not allowed in this form
CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
    LANGUAGE SQL
    RETURN x[1];
ERROR:  SQL function with unquoted function body cannot have polymorphic arguments
-- check reporting of parse-analysis errors
CREATE FUNCTION functest_S_xx(x date) RETURNS boolean
    LANGUAGE SQL
    RETURN x > 1;
ERROR:  operator does not exist: date > integer
LINE 3:     RETURN x > 1;
                     ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
-- tricky parsing
CREATE FUNCTION functest_S_15(x int) RETURNS boolean
LANGUAGE SQL
BEGIN ATOMIC
    select case when x % 2 = 0 then true else false end;
END;
SELECT functest_S_1('abcd', '2020-01-01');
 functest_s_1 
--------------
 t
(1 row)

SELECT functest_S_2(ARRAY['1', '2', '3']);
 functest_s_2 
--------------
            1
(1 row)

SELECT functest_S_3();
 functest_s_3 
--------------
 f
(1 row)

SELECT functest_S_10('abcd', '2020-01-01');
 functest_s_10 
---------------
 t
(1 row)

SELECT functest_S_13();
 functest_s_13 
---------------
 f
(1 row)

SELECT pg_get_functiondef('functest_S_1'::regproc);
                           pg_get_functiondef                           
------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_1(a text, b date)+
  RETURNS boolean                                                      +
  LANGUAGE sql                                                         +
 RETURN ((a = 'abcd'::text) AND (b > '01-01-2001'::date))              +
 
(1 row)

SELECT pg_get_functiondef('functest_S_2'::regproc);
                        pg_get_functiondef                        
------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_2(a text[])+
  RETURNS integer                                                +
  LANGUAGE sql                                                   +
 RETURN ((a)[1])::integer                                        +
 
(1 row)

SELECT pg_get_functiondef('functest_S_3'::regproc);
                    pg_get_functiondef                    
----------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3()+
  RETURNS boolean                                        +
  LANGUAGE sql                                           +
 RETURN false                                            +
 
(1 row)

SELECT pg_get_functiondef('functest_S_3a'::regproc);
                    pg_get_functiondef                     
-----------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3a()+
  RETURNS boolean                                         +
  LANGUAGE sql                                            +
 BEGIN ATOMIC                                             +
  RETURN false;                                           +
 END                                                      +
 
(1 row)

SELECT pg_get_functiondef('functest_S_10'::regproc);
                           pg_get_functiondef                            
-------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date)+
  RETURNS boolean                                                       +
  LANGUAGE sql                                                          +
 BEGIN ATOMIC                                                           +
  SELECT ((a = 'abcd'::text) AND (b > '01-01-2001'::date));             +
 END                                                                    +
 
(1 row)

SELECT pg_get_functiondef('functest_S_13'::regproc);
                    pg_get_functiondef                     
-----------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_13()+
  RETURNS boolean                                         +
  LANGUAGE sql                                            +
 BEGIN ATOMIC                                             +
  SELECT 1;                                               +
  SELECT false;                                           +
 END                                                      +
 
(1 row)

SELECT pg_get_functiondef('functest_S_15'::regproc);
                         pg_get_functiondef                         
--------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_15(x integer)+
  RETURNS boolean                                                  +
  LANGUAGE sql                                                     +
 BEGIN ATOMIC                                                      +
  SELECT                                                           +
          CASE                                                     +
              WHEN ((x % 2) = 0) THEN true                         +
              ELSE false                                           +
          END AS "case";                                           +
 END                                                               +
 
(1 row)

SELECT pg_get_functiondef('functest_S_16'::regproc);
                              pg_get_functiondef                               
-------------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION temp_func_test.functest_s_16(a integer, b integer)+
  RETURNS void                                                                +
  LANGUAGE sql                                                                +
 BEGIN ATOMIC                                                                 +
  INSERT INTO functest1 (i)  SELECT (functest_s_16.a + functest_s_16.b);      +
 END                                                                          +
 
(1 row)

DROP TABLE functest1 CASCADE;
NOTICE:  drop cascades to function functest_s_16(integer,integer)
-- test with views
CREATE TABLE functest3 (a int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO functest3 VALUES (1), (2);
CREATE VIEW functestv3 AS SELECT * FROM functest3;
CREATE FUNCTION functest_S_14() RETURNS bigint
    RETURN (SELECT count(*) FROM functestv3);
SELECT functest_S_14();
 functest_s_14 
---------------
             2
(1 row)

DROP TABLE functest3 CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to view functestv3
drop cascades to function functest_s_14()
-- information_schema tests
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
    RETURNS int
    LANGUAGE SQL
    AS 'SELECT $1 + $2';
CREATE FUNCTION functest_IS_2(out a int, b int default 1)
    RETURNS int
    LANGUAGE SQL
    AS 'SELECT $1';
CREATE FUNCTION functest_IS_3(a int default 1, out b int)
    RETURNS int
    LANGUAGE SQL
    AS 'SELECT $1';
SELECT routine_name, ordinal_position, parameter_name, parameter_default
    FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name)
    WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_'
    ORDER BY 1, 2;
 routine_name  | ordinal_position | parameter_name | parameter_default 
---------------+------------------+----------------+-------------------
 functest_is_1 |                1 | a              | 
 functest_is_1 |                2 | b              | 1
 functest_is_1 |                3 | c              | 'foo'::text
 functest_is_2 |                1 | a              | 
 functest_is_2 |                2 | b              | 1
 functest_is_3 |                1 | a              | 1
 functest_is_3 |                2 | b              | 
(7 rows)

DROP FUNCTION functest_IS_1(int, int, text), functest_IS_2(int), functest_IS_3(int);
-- routine usage views
CREATE FUNCTION functest_IS_4a() RETURNS int LANGUAGE SQL AS 'SELECT 1';
CREATE FUNCTION functest_IS_4b(x int DEFAULT functest_IS_4a()) RETURNS int LANGUAGE SQL AS 'SELECT x';
CREATE SEQUENCE functest1;
CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1'))
    RETURNS int
    LANGUAGE SQL
    AS 'SELECT x';
CREATE FUNCTION functest_IS_6()
    RETURNS int
    LANGUAGE SQL
    RETURN nextval('functest1');
CREATE TABLE functest2 (a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE FUNCTION functest_IS_7()
    RETURNS int
    LANGUAGE SQL
    RETURN (SELECT count(a) FROM functest2);
SELECT r0.routine_name, r1.routine_name
  FROM information_schema.routine_routine_usage rru
       JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name
       JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name
  WHERE r0.routine_schema = 'temp_func_test' AND
        r1.routine_schema = 'temp_func_test'
  ORDER BY 1, 2;
  routine_name  |  routine_name  
----------------+----------------
 functest_is_4b | functest_is_4a
(1 row)

SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage
  WHERE routine_schema = 'temp_func_test'
  ORDER BY 1, 2;
 routine_name  | sequence_name 
---------------+---------------
 functest_is_5 | functest1
 functest_is_6 | functest1
(2 rows)

SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage
  WHERE routine_schema = 'temp_func_test'
  ORDER BY 1, 2;
 routine_name  | table_name | column_name 
---------------+------------+-------------
 functest_is_7 | functest2  | a
(1 row)

SELECT routine_name, table_name FROM information_schema.routine_table_usage
  WHERE routine_schema = 'temp_func_test'
  ORDER BY 1, 2;
 routine_name  | table_name 
---------------+------------
 functest_is_7 | functest2
(1 row)

DROP FUNCTION functest_IS_4a CASCADE;
NOTICE:  drop cascades to function functest_is_4b(integer)
DROP SEQUENCE functest1 CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function functest_is_5(integer)
drop cascades to function functest_is_6()
DROP TABLE functest2 CASCADE;
NOTICE:  drop cascades to function functest_is_7()
-- overload
CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql'
       IMMUTABLE AS 'SELECT $1 > 0';
DROP FUNCTION functest_b_1;
DROP FUNCTION functest_b_1;  -- error, not found
ERROR:  could not find a function named "functest_b_1"
DROP FUNCTION functest_b_2;  -- error, ambiguous
ERROR:  function name "functest_b_2" is not unique
HINT:  Specify the argument list to select the function unambiguously.
-- CREATE OR REPLACE tests
CREATE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL AS 'SELECT $1';
CREATE OR REPLACE FUNCTION functest1(a int) RETURNS int LANGUAGE SQL WINDOW AS 'SELECT $1';
ERROR:  cannot change routine kind
DETAIL:  "functest1" is a function.
CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1';
ERROR:  cannot change routine kind
DETAIL:  "functest1" is a function.
DROP FUNCTION functest1(a int);
-- inlining of set-returning functions
CREATE TABLE functest3 (a int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
INSERT INTO functest3 VALUES (1), (2), (3);
CREATE FUNCTION functest_sri1() RETURNS SETOF int
LANGUAGE SQL
STABLE
AS '
    SELECT * FROM functest3;
';
SELECT * FROM functest_sri1();
 functest_sri1 
---------------
             1
             2
             3
(3 rows)

EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1();
                 QUERY PLAN                 
--------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Output: functest3.a
   ->  Seq Scan on temp_func_test.functest3
         Output: functest3.a
 Settings: optimizer = 'off'
 Optimizer: Postgres query optimizer
(6 rows)

CREATE FUNCTION functest_sri2() RETURNS SETOF int
LANGUAGE SQL
STABLE
BEGIN ATOMIC
    SELECT * FROM functest3;
END;
SELECT * FROM functest_sri2();
 functest_sri2 
---------------
             1
             2
             3
(3 rows)

EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2();
                 QUERY PLAN                 
--------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   Output: functest3.a
   ->  Seq Scan on temp_func_test.functest3
         Output: functest3.a
 Settings: optimizer = 'off'
 Optimizer: Postgres query optimizer
(6 rows)

DROP TABLE functest3 CASCADE;
NOTICE:  drop cascades to function functest_sri2()
-- Check behavior of VOID-returning SQL functions
CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS
$$ SELECT a + 1 $$;
SELECT voidtest1(42);
 voidtest1 
-----------
 
(1 row)

CREATE FUNCTION voidtest2(a int, b int) RETURNS VOID LANGUAGE SQL AS
$$ SELECT voidtest1(a + b) $$;
SELECT voidtest2(11,22);
 voidtest2 
-----------
 
(1 row)

-- currently, we can inline voidtest2 but not voidtest1
EXPLAIN (verbose, costs off) SELECT voidtest2(11,22);
             QUERY PLAN              
-------------------------------------
 Result
   Output: voidtest1(33)
 Settings: optimizer = 'off'
 Optimizer: Postgres query optimizer
(4 rows)

CREATE TEMP TABLE sometable(f1 int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE FUNCTION voidtest3(a int) RETURNS VOID LANGUAGE SQL AS
$$ INSERT INTO sometable VALUES(a + 1) $$;
SELECT voidtest3(17);
 voidtest3 
-----------
 
(1 row)

CREATE FUNCTION voidtest4(a int) RETURNS VOID LANGUAGE SQL AS
$$ INSERT INTO sometable VALUES(a - 1) RETURNING f1 $$;
SELECT voidtest4(39);
 voidtest4 
-----------
 
(1 row)

TABLE sometable;
 f1 
----
 38
 18
(2 rows)

CREATE FUNCTION voidtest5(a int) RETURNS SETOF VOID LANGUAGE SQL AS
$$ SELECT generate_series(1, a) $$ STABLE;
SELECT * FROM voidtest5(3);
 voidtest5 
-----------
(0 rows)

-- Regression tests for bugs:
-- Check that arguments that are R/W expanded datums aren't corrupted by
-- multiple uses.  This test knows that array_append() returns a R/W datum
-- and will modify a R/W array input in-place.  We use SETOF to prevent
-- inlining of the SQL function.
CREATE FUNCTION double_append(anyarray, anyelement) RETURNS SETOF anyarray
LANGUAGE SQL IMMUTABLE AS
$$ SELECT array_append($1, $2) || array_append($1, $2) $$;
SELECT double_append(array_append(ARRAY[q1], q2), q3)
  FROM (VALUES(1,2,3), (4,5,6)) v(q1,q2,q3);
 double_append 
---------------
 {1,2,3,1,2,3}
 {4,5,6,4,5,6}
(2 rows)

-- Things that shouldn't work:
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
    AS 'SELECT ''not an integer'';';
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Actual return type is text.
CONTEXT:  SQL function "test1"
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
    AS 'not even SQL';
ERROR:  syntax error at or near "not"
LINE 2:     AS 'not even SQL';
                ^
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
    AS 'SELECT 1, 2, 3;';
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Final statement must return exactly one column.
CONTEXT:  SQL function "test1"
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
    AS 'SELECT $2;';
ERROR:  there is no parameter $2
LINE 2:     AS 'SELECT $2;';
                       ^
CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL
    AS 'a', 'b';
ERROR:  only one AS item needed for language "sql"
-- Cleanup
DROP SCHEMA temp_func_test CASCADE;
NOTICE:  drop cascades to 30 other objects
DETAIL:  drop cascades to function functest_a_1(text,date)
drop cascades to function functest_a_2(text[])
drop cascades to function functest_a_3()
drop cascades to function functest_b_2(integer)
drop cascades to function functest_b_3(integer)
drop cascades to function functest_b_4(integer)
drop cascades to function functest_c_1(integer)
drop cascades to function functest_c_2(integer)
drop cascades to function functest_c_3(integer)
drop cascades to function functest_e_1(integer)
drop cascades to function functest_e_2(integer)
drop cascades to function functest_f_1(integer)
drop cascades to function functest_f_2(integer)
drop cascades to function functest_f_3(integer)
drop cascades to function functest_f_4(integer)
drop cascades to function functest_s_1(text,date)
drop cascades to function functest_s_2(text[])
drop cascades to function functest_s_3()
drop cascades to function functest_s_3a()
drop cascades to function functest_s_10(text,date)
drop cascades to function functest_s_13()
drop cascades to function functest_s_15(integer)
drop cascades to function functest_b_2(bigint)
drop cascades to function functest_sri1()
drop cascades to function voidtest1(integer)
drop cascades to function voidtest2(integer,integer)
drop cascades to function voidtest3(integer)
drop cascades to function voidtest4(integer)
drop cascades to function voidtest5(integer)
drop cascades to function double_append(anyarray,anyelement)
DROP USER regress_unpriv_user;
RESET search_path;