In the DOC only described how to change function definition.
But I have only function body changed (text between $$
sql $$
).
How to replace only this function body? Should I use CREATE OR REPLACE
syntax to accomplish this?
Yes, you can update the definition of the function using the Postgres CREATE OR REPLACE FUNCTION
syntax described in the documentation for CREATE FUNCTION.
So if you've got a function you could replace it by re-declaring it. For instance, here's how I used this to replace id_generator
after a schema change:
ALTER SCHEMA public RENAME TO app;
CREATE OR REPLACE FUNCTION app.id_generator(OUT result bigint) RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
our_epoch bigint := 1111111111111;
seq_id bigint;
now_millis bigint;
-- the id of this DB shard, must be set for each
-- schema shard you have - you could pass this as a parameter too
shard_id int := 1;
BEGIN
SELECT nextval('app.global_id_sequence') % 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$$;
The result of which changed the function in place without needing to update tables that relied on the function.