Create the aggregate if not exists

Question

in my sql script I want to return with the last query some records. For that query I'm using a aggregate:

CREATE AGGREGATE array_agg_mult(anyarray) (
    SFUNC = array_cat,
    STYPE = anyarray,
    INITCOND = '{}'
);

So if I call the script twice I have to drop the aggregate at the end. But the script should end with a custom sql query. Is there a way to say something like CREATE OR REPLACE?

Answer 1

You could simply DROP AGGREGATE IF EXISTS array_agg_mult(anyarray); prior to your CREATE AGGREGATE

Note that this may throw an error if you change the parameters/signature, so you'd need to adjust for that if you do.

Manual Reference: https://www.postgresql.org/docs/current/static/sql-dropaggregate.html

Answer 2

DO $$ BEGIN
CREATE AGGREGATE array_agg_mult(anyarray) (
    SFUNC = array_cat,
    STYPE = anyarray,
    INITCOND = '{}'
);
EXCEPTION
    WHEN duplicate_function THEN NULL;
END $$;

seems to work as well.