OR REPLACE and DROP in SQL

Function lifecycle: create it, replace the body in place, drop it by signature.

-- OR REPLACE swaps a body in place; DROP takes the whole signature.
CREATE FUNCTION shipping_promise()
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE
RETURN 'ships in 5 days';

CREATE OR REPLACE FUNCTION shipping_promise()
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE
RETURN 'ships tomorrow';

SELECT shipping_promise() AS promise;

DROP FUNCTION shipping_promise();

How it works

1. The first CREATE promises five days; CREATE OR REPLACE upgrades to tomorrow.
2. Replacing keeps the function's identity — callers never notice.
3. DROP FUNCTION shipping_promise() names the signature, not just the name.

Keywords and builtins used here

• AS
• CREATE
• DROP
• FUNCTION
• IMMUTABLE
• LANGUAGE
• OR
• REPLACE
• RETURN
• RETURNS
• SELECT
• SQL
• TEXT

The run, in numbers

Lines

16

Characters to type

347

Tokens

42

Three-star pace

70 tpm

At the three-star pace of 70 tokens a minute, this run takes about 36 seconds.

Type this snippet

Step 4 of 4 in Procedures & transactions, step 12 of 23 in PostgreSQL Stored Procedures.

← Previous Next →