CREATE SERVER udf_loopback FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'udf_test', driver 'binary');
CREATE USER MAPPING FOR CURRENT_USER SERVER udf_loopback;
CREATE SERVER udf_admin FOREIGN DATA WRAPPER clickhouse_fdw;
CREATE USER MAPPING FOR CURRENT_USER SERVER udf_admin;
CALL clickhouse_perform('udf_admin', 'DROP DATABASE IF EXISTS udf_test');
CALL clickhouse_perform('udf_admin', 'CREATE DATABASE udf_test');
CALL clickhouse_perform('udf_admin', $$
    CREATE TABLE udf_test.vals (
        id UInt8
    ) ENGINE = TinyLog
$$);
CALL clickhouse_perform('udf_admin', $$
    INSERT INTO udf_test.vals VALUES (1), (2), (3)
$$);
CREATE FOREIGN TABLE udf_vals (
    id int
) SERVER udf_loopback OPTIONS (table_name 'vals');
CREATE FUNCTION keep_local(bigint) RETURNS bigint LANGUAGE plpgsql AS $$
BEGIN
    RETURN $1;
END;
$$;
-- pg_clickhouse keeps unknown functions local.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE keep_local(id) = 1;
                    QUERY PLAN                     
---------------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (keep_local((udf_vals.id)::bigint) = 1)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT keep_local(sum(id)) FROM udf_vals;
                      QUERY PLAN                       
-------------------------------------------------------
 Foreign Scan
   Output: keep_local((sum(id)))
   Relations: Aggregate on (udf_vals)
   Remote SQL: SELECT sumOrNull(id) FROM udf_test.vals
(4 rows)

SELECT keep_local(sum(id)) FROM udf_vals;
 keep_local 
------------
          6
(1 row)

-- Keep pg_clickhouse's own un-shippable functions local.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE pgch_version() = '';
                 QUERY PLAN                 
--------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (pgch_version() = ''::text)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE clickhouse_server_version('') = '';
                         QUERY PLAN                         
------------------------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (clickhouse_server_version(''::text) = ''::text)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE clickhouse_fdw_validator('{x}'::text[], 0) IS NULL;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (clickhouse_fdw_validator('{x}'::text[], '0'::oid) IS NULL)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE ch_noop_bigint('1') = 1;
                 QUERY PLAN                 
--------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (ch_noop_bigint('1'::text) = 1)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE ch_noop_float8('1') = 1.1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (ch_noop_float8('1'::text) = '1.1'::double precision)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM udf_vals WHERE ch_noop_float8_float8('1', 1.1) = 1.1;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Foreign Scan on public.udf_vals
   Output: id
   Filter: (ch_noop_float8_float8('1'::text, '1.1'::double precision) = '1.1'::double precision)
   Remote SQL: SELECT id FROM udf_test.vals
(4 rows)

\unset ECHO
-- Keep unknown extension's functions local.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT * FROM remote_sql($$ SELECT id FROM udf_vals WHERE set_limit(id) = '1' $$);
           remote_sql           
--------------------------------
 "SELECT id FROM udf_test.vals"
(1 row)

-- Keep unknown extension's types and operators local.
CREATE EXTENSION IF NOT EXISTS citext;
SELECT * FROM remote_sql($$ SELECT id FROM udf_vals WHERE citext(id) = '1' $$);
           remote_sql           
--------------------------------
 "SELECT id FROM udf_test.vals"
(1 row)

SELECT * FROM remote_sql($$ SELECT id FROM udf_vals WHERE citext(id) IS NULL $$);
           remote_sql           
--------------------------------
 "SELECT id FROM udf_test.vals"
(1 row)

-- Wait for a lock before loading intarray.
SELECT pg_advisory_lock(hashtext('intarray'));
 pg_advisory_lock 
------------------
 
(1 row)

CREATE EXTENSION intarray;
-- Keep unknown functions from known extension local.
SELECT * FROM remote_sql('SELECT id FROM udf_vals WHERE intset(id) = ARRAY[1]');
           remote_sql           
--------------------------------
 "SELECT id FROM udf_test.vals"
(1 row)

-- Keep unmapped operators from known extension local.
SELECT * FROM remote_sql('SELECT id FROM udf_vals WHERE ARRAY[id] @> ARRAY[1]');
           remote_sql           
--------------------------------
 "SELECT id FROM udf_test.vals"
(1 row)

-- Drop intarray so it doesn't mess with other tests (array_functions.sql).
DROP EXTENSION intarray;
SELECT pg_advisory_unlock(hashtext('intarray'));
 pg_advisory_unlock 
--------------------
 t
(1 row)

CALL clickhouse_perform('udf_admin', 'DROP DATABASE udf_test');
DROP USER MAPPING FOR CURRENT_USER SERVER udf_loopback;
DROP USER MAPPING FOR CURRENT_USER SERVER udf_admin;
DROP SERVER udf_loopback CASCADE;
NOTICE:  drop cascades to foreign table udf_vals
DROP SERVER udf_admin CASCADE;