0

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?

1 Answer 1

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.

3
  • 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
    Sep 26, 2023 at 12:40
  • 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.
    – chris000r
    Sep 26, 2023 at 12:52
  • 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. Sep 26, 2023 at 12:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.