Swapping Tables With Dependencies in Postgres: Part 2

So after briefly looking into how views are stored, let’s do the same for functions. Hopefully, any references aren’t mutated whenever an ALTER statement is executed.

First, let’s create a function that grabs the same info we were trying to grab in the earlier post:

-- create a function to grab the detailed_message info
CREATE FUNCTION fn_detailed_message() RETURNS TABLE(
    id int,
    content varchar,
    sent timestamp,
    received timestamp,
    recipient_first_name varchar,
    recipient_last_name varchar,
    recipient_phone_number varchar
) AS $$
    SELECT message.id,
           content,
           sent,
           received,
           contact.first_name AS recipient_first_name,
           contact.last_name AS recipient_last_name,
           contact.phone_number AS recipient_phone_number
    FROM contact JOIN message ON message.recipient_id = contact.id;
$$ LANGUAGE SQL;

Arguably, there’s some hand-waving happening here with the RETURNS TABLE, etc. so here are the docs just in case. Otherwise, hopefully the above is pretty self-explanatory.

Now that we have a function set up, let’s check the definition using \df+ fn_detailed_message:

>> \df+ fn_detailed_message
...
Source code         |                                                                          
                    |     SELECT message.id,                                                   
                    |            content,                                                      
                    |            sent,                                                         
                    |            received,                                                     
                    |            contact.first_name AS recipient_first_name,                   
                    |            contact.last_name AS recipient_last_name,                     
                    |            contact.phone_number AS recipient_phone_number                
                    |     FROM contact JOIN message ON message.recipient_id = contact.id;

Which looks just as we thought it would.

Now let’s try altering the name of the message table and then check the definition again:

>> ALTER TABLE message RENAME TO message_stale
ALTER TABLE

>> \df+ fn_detailed_message
...
Source code         |                                                                                                     
                    |     SELECT message.id,                                                                              
                    |            content,                                                                                 
                    |            sent,                                                                                    
                    |            received,                                                                                
                    |            contact.first_name AS recipient_first_name,                                              
                    |            contact.last_name AS recipient_last_name,                                                
                    |            contact.phone_number AS recipient_phone_number                                           
                    |     FROM contact JOIN message ON message.recipient_id = contact.id;

And it looks the same! So we’ve found a significant difference here in how materialized/views and functions are stored. Just to make sure, let’s try querying the function to see what postgres tells us:

>> SELECT * FROM fn_detailed_message();
ERROR:  relation "message" does not exist
LINE 9:     FROM contact JOIN message ON message.recipient_id = cont...
                              ^
QUERY:  
    SELECT message.id,
           content,
           sent,
           received,
           contact.first_name AS recipient_first_name,
           contact.last_name AS recipient_last_name,
           contact.phone_number AS recipient_phone_number
    FROM contact JOIN message ON message.recipient_id = contact.id;

CONTEXT:  SQL function "fn_detailed_message" during startup

And expectedly, there is an error! This kind of behavior is what I was expecting when dealing with views, but I can understand the walls guarding views and their dependencies.

To sum things up, it is not possible to swap tables in postgres without altering existing dependencies using a view or materialized view. You can do so with a function though! Just remember to handle the edge case where that relation is missing.