15

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?

1
  • 3
    Please be more specific,AFAIK If a function's params or language or return type has to change then you should drop the function before alter otherwise CREATE OR REPLACE will work Commented Jan 26, 2017 at 10:10

1 Answer 1

20

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.