postgres db 'function mul(double precision) does not exist'

Question

I am currently facing a problem on an postgres database. The db server is a data warehouse for a software we are using. For every tenant in the software system there is a database on the postgres server. The software exports data on a frequent schedule. All right.

For some analytics, there are some views defined on the tables in each database (so for each tenant we have). There is one view defined like:

SELECT table.id, 
...
mul(table.modifier) as "mod",
...
FROM table
GROUP BY id

I shrinked that definition a little bit to the most interesting part. That definition works on all current tenants. I was creating a new one with a fresh database. If I want to save the definition, pgAdmin keeps saying: 'ERROR: function mul(double precision) odes not exist LINE: x mul(...) HINT: No function matches the given name and argument types. You might need to add explicit type cast'.

All right. I searched through the old databases to check if there is a function, symbol or what ever called mul. I did not found anything. Is there anybody out there who can help?

Answer 1

There must be a custom aggregate function mul defined in your old database. Using the psql client, you can find the aggregate with

\da *.mul

To get the definition of the aggregate (and its dependent functions), you can use a schema-only pg_dump of the old database. The definitions are somewhere in there.

• chris000r: Ah all right. That did a trick to me. In an old db I can see that there is in schema public an aggregate called mul. How can I 'export' the definition to a new db? Where can I find that object in pgadmin?
• chris000r: I was able to create a script from the definition and could use that in the new db to create the aggregate. Thank you very much. In newer versions of pgadmin I am also able to see the aggregates. The old one was not able to. Thank you very much.
• Laurenz Albe: In pgAdmin, you probably have to search for an "aggregates" section in the schema. You can export the definition with pg_dump, as indicated in the answer. You cannot dump only the aggregate function, you have to dump the whole schema definition and get the aggregate from that.