How to replace postgresql function body?

Question

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?

Powerhouse

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 – Mangrove

Answer 1

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.

Arlinda