--
-- 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;
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);
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);
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);
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);
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                   
-----------------------------------------------
 Function Scan on temp_func_test.functest_sri1
   Output: functest_sri1
   Function Call: functest_sri1()
 Optimizer: GPORCA
(4 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                   
-----------------------------------------------
 Function Scan on temp_func_test.functest_sri2
   Output: functest_sri2
   Function Call: functest_sri2()
 Optimizer: GPORCA
(4 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)
(2 rows)

CREATE TEMP TABLE sometable(f1 int);
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 
----
 18
 38
(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;