Demo showing some differences between sql vs plpgsql PostgreSQL functions

sql_vs_plpgsql_functions_demo.sql

--
-- 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 ~ %

*/