postgresqlcreate-function

How to replace postgresql function body?


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?


Solution

  • 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.