joelonsql · June 2, 2025 19:47 · Jun 2, 2025
diff --git a/sql_vs_plpgsql_functions_demo.sql b/sql_vs_plpgsql_functions_demo.sql
@@ -0,0 +1,171 @@
+--
+-- sql function demo
+--
+
+CREATE TABLE example_table
+(
+    id INT NOT NULL,
+    name TEXT NOT NULL,
+    PRIMARY KEY (id)
+);
+
+INSERT INTO example_table (id, name) VALUES (1, 'Adam');
+
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+BEGIN ATOMIC
+    SELECT t.name FROM example_table_mispelled t WHERE t.id = get_name.id;
+END;
+
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+BEGIN ATOMIC
+    SELECT t.name FROM example_table t WHERE t.id = get_name.id;
+END;
+
+DROP TABLE example_table;
+
+DROP TABLE example_table CASCADE; -- force dropping
+
+--
+-- plpgsql function demo
+--
+
+CREATE TABLE example_table
+(
+    id INT NOT NULL,
+    name TEXT NOT NULL,
+    PRIMARY KEY (id)
+);
+
+INSERT INTO example_table (id, name) VALUES (1, 'Adam');
+
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN (SELECT t.name FROM example_table_mispelled t WHERE t.id = get_name.id);
+END;
+$$;
+
+-- no compile-time error
+
+SELECT get_name(1);
+
+-- run-time error
+
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN (SELECT t.name FROM example_table t WHERE t.id = get_name.id);
+END;
+$$;
+
+SELECT get_name(1);
+
+DROP TABLE example_table;
+
+-- no error
+
+SELECT get_name(1);
+
+-- run-time error
+
+/*
+
+joel@Joels-MacBook-Pro ~ % psql -a -f ~/example.sql
+-- set client_min_messages = log;
+-- set debug_print_parse to on;
+--
+-- sql function demo
+--
+CREATE TABLE example_table
+(
+    id INT NOT NULL,
+    name TEXT NOT NULL,
+    PRIMARY KEY (id)
+);
+CREATE TABLE
+INSERT INTO example_table (id, name) VALUES (1, 'Adam');
+INSERT 0 1
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+BEGIN ATOMIC
+    SELECT t.name FROM example_table_mispelled t WHERE t.id = get_name.id;
+END;
+psql:/Users/joel/example.sql:18: ERROR:  relation "example_table_mispelled" does not exist
+LINE 4:     SELECT t.name FROM example_table_mispelled t WHERE t.id ...
+                               ^
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+BEGIN ATOMIC
+    SELECT t.name FROM example_table t WHERE t.id = get_name.id;
+END;
+CREATE FUNCTION
+DROP TABLE example_table;
+psql:/Users/joel/example.sql:26: ERROR:  cannot drop table example_table because other objects depend on it
+DETAIL:  function get_name(integer) depends on table example_table
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+DROP TABLE example_table CASCADE; -- force dropping
+psql:/Users/joel/example.sql:28: NOTICE:  drop cascades to function get_name(integer)
+DROP TABLE
+--
+-- plpgsql function demo
+--
+CREATE TABLE example_table
+(
+    id INT NOT NULL,
+    name TEXT NOT NULL,
+    PRIMARY KEY (id)
+);
+CREATE TABLE
+INSERT INTO example_table (id, name) VALUES (1, 'Adam');
+INSERT 0 1
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN (SELECT t.name FROM example_table_mispelled t WHERE t.id = get_name.id);
+END;
+$$;
+CREATE FUNCTION
+-- no compile-time error
+SELECT get_name(1);
+psql:/Users/joel/example.sql:54: ERROR:  relation "example_table_mispelled" does not exist
+LINE 1: (SELECT t.name FROM example_table_mispelled t WHERE t.id = g...
+                            ^
+QUERY:  (SELECT t.name FROM example_table_mispelled t WHERE t.id = get_name.id)
+CONTEXT:  PL/pgSQL function get_name(integer) line 3 at RETURN
+-- run-time error
+CREATE OR REPLACE FUNCTION get_name(id INT)
+RETURNS TEXT
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN (SELECT t.name FROM example_table t WHERE t.id = get_name.id);
+END;
+$$;
+CREATE FUNCTION
+SELECT get_name(1);
+ get_name
+----------
+ Adam
+(1 row)
+
+DROP TABLE example_table;
+DROP TABLE
+-- no error
+SELECT get_name(1);
+psql:/Users/joel/example.sql:73: ERROR:  relation "example_table" does not exist
+LINE 1: (SELECT t.name FROM example_table t WHERE t.id = get_name.id...
+                            ^
+QUERY:  (SELECT t.name FROM example_table t WHERE t.id = get_name.id)
+CONTEXT:  PL/pgSQL function get_name(integer) line 3 at RETURN
+-- run-time error
+joel@Joels-MacBook-Pro ~ %
+
+*/