routine_dep_tests.md

statement ok
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT 1; $$;
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT g(); $$;

query I
SELECT f();
----
1

subtest drop_referencing

statement ok
DROP FUNCTION f();

statement error pgcode 42883 pq: unknown function: f\(\): function undefined
SELECT f();

query I
SELECT g();
----
1

subtest end

subtest rename_referenced

statement ok
ALTER FUNCTION g RENAME TO h;

# TODO(drewk): it should not be possible to rename the referenced function.
statement error pgcode 42883 pq: unknown function: f\(\): function undefined
SELECT f();

statement ok
DROP FUNCTION h;

subtest end

subtest drop_referenced

statement ok
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT 1; $$;
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT g(); $$;
DROP FUNCTION g();

# TODO(drewk): it should not be possible to drop the referenced function.
statement error pgcode 42883 pq: unknown function: public.g\(\): function undefined
SELECT f();

subtest end

subtest alter_referenced_schema

statement ok
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT 1; $$;
CREATE SCHEMA foo;

statement ok
ALTER FUNCTION g SET SCHEMA foo;

# TODO(drewk): it should not be possible to alter the referenced function's
# schema.
statement error pgcode 42883 pq: unknown function: public.g\(\): function undefined
SELECT f();

subtest end

subtest rename_referenced_schema

statement ok
DROP FUNCTION f();
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT foo.g(); $$;

query I
SELECT f();
----
1

statement ok
ALTER SCHEMA foo RENAME TO bar;

# TODO(drewk): it should not be possible to rename the referenced function's
# schema.
statement error pgcode 3F000 pq: schema \"foo\" does not exist
SELECT f();

subtest end

subtest drop_referenced_schema

statement ok
DROP FUNCTION f();
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT bar.g(); $$;

statement error pgcode 2BP01 pq: schema \"bar\" is not empty and CASCADE was not specified
DROP SCHEMA bar;

statement ok
DROP SCHEMA bar CASCADE;

# TODO(drewk): f() should be dropped too.
statement error pgcode 3F000 pq: schema \"bar\" does not exist
SELECT f();

subtest end

subtest cross_db_reference

statement ok
DROP FUNCTION f();
CREATE DATABASE db;

statement error pgcode 0A000 pq: cross-database function references not allowed
CREATE FUNCTION db.g() RETURNS INT LANGUAGE SQL AS $$ SELECT 2; $$;

statement ok
USE db;
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT 2; $$;
USE test;

statement error pgcode 3F000 pq: schema \"db\" does not exist
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT db.g(); $$;

statement error pgcode 0A000 pq: cross-database function references not allowed
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT db.public.g(); $$;

statement ok
DROP DATABASE db CASCADE;

subtest end

subtest transitive_table_reference

statement ok
CREATE TABLE t (a INT, b INT);
INSERT INTO t VALUES (1, 2);
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT a FROM t; $$;
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT g(); $$;

query I
SELECT f();
----
1

statement error pgcode 2BP01 pq: cannot rename relation \"t\" because function \"g\" depends on it
ALTER TABLE t RENAME TO t2;

statement error pgcode 2BP01 pq: cannot drop table t because other objects depend on it
DROP TABLE t;

statement ok
DROP TABLE t CASCADE;

statement error pgcode 42883 pq: unknown function: g\(\): function undefined
SELECT g();

statement error pgcode 42883 pq: unknown function: f\(\): function undefined
SELECT f();

subtest end

subtest transitive_view_reference

statement ok
CREATE TABLE t (a INT, b INT);
INSERT INTO t VALUES (1, 2);
CREATE VIEW v AS SELECT a FROM t;
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT * FROM v; $$;
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT g(); $$;

query I
SELECT f();
----
1

query error pgcode 2BP01 pq: cannot drop relation \"t\" because view \"v\" depends on it
DROP TABLE t;

statement ok
DROP TABLE t CASCADE;

statement error pgcode 42883 pq: unknown function: f\(\): function undefined
SELECT f();

subtest end

subtest transitive_schema_reference

statement ok
CREATE SCHEMA bar;
CREATE TABLE bar.t (a INT, b INT);
INSERT INTO bar.t VALUES (1, 2);
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT a FROM bar.t; $$;
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT g(); $$;

query I
SELECT f();
----
1

statement error pgcode 2BP01 pq: cannot rename schema because relation \"test.public.g\" depends on relation \"test.bar.t\"
ALTER SCHEMA bar RENAME TO baz;

statement error pgcode 2BP01 pq: schema \"bar\" is not empty and CASCADE was not specified
DROP SCHEMA bar;

statement ok
DROP SCHEMA bar CASCADE;

statement error pgcode 42883 pq: unknown function: g\(\): function undefined
SELECT g();

statement error pgcode 42883 pq: unknown function: f\(\): function undefined
SELECT f();

subtest end

subtest transitive_type_reference

statement ok
CREATE TYPE typ AS ENUM ('hello');
CREATE FUNCTION g() RETURNS TEXT LANGUAGE SQL AS $$ SELECT 'hello'::typ::TEXT; $$;
CREATE FUNCTION f() RETURNS TEXT LANGUAGE SQL AS $$ SELECT g(); $$;

query T
SELECT f();
----
hello

# Types are rewritten as ID references, so this succeeds.
statement ok
ALTER TYPE typ RENAME TO typ2;

query T
SELECT f();
----
hello

statement error pgcode 2BP01 pq: cannot drop type "typ2" because other objects \(\[test.public.g test.public.f\]\) still depend on it
DROP TYPE typ2;

statement error pgcode 0A000 pq: unimplemented: DROP TYPE CASCADE is not yet supported
DROP TYPE typ2 CASCADE;

statement ok
DROP FUNCTION f();
DROP FUNCTION g();
DROP TYPE typ2;

subtest end

subtest transitive_function_reference

statement ok
CREATE FUNCTION h() RETURNS INT LANGUAGE SQL AS $$ SELECT 100; $$;
CREATE FUNCTION g() RETURNS INT LANGUAGE SQL AS $$ SELECT h(); $$;
CREATE FUNCTION f() RETURNS INT LANGUAGE SQL AS $$ SELECT g(); $$;

query I
SELECT f();
----
100

statement ok
ALTER FUNCTION h RENAME TO h2;

# TODO(drewk): it shouldn't be possible to rename h().
statement error pgcode 42883 pq: unknown function: public.h\(\): function undefined
SELECT f();

statement ok
ALTER FUNCTION h2 RENAME TO h;

query I
SELECT f();
----
100

statement ok
DROP FUNCTION h;

# TODO(drewk): it shouldn't be possible to drop h().
statement error pgcode 42883 pq: unknown function: public.h\(\): function undefined
SELECT f();

subtest end