0

I am a software engineer studying PostgreSQL. PostgreSQL supports lots of languages (e.g. plpgsql). I don't see an easy way to install storied procedures. Is there some sort of pip or npm analog? I found abandoned pex tool.

PostgreSQL can define functions programmatically. So it must be trivial to write a function as pg_install_library taking a link to github repo - pulling files with plpgsql functions, types, then checking declared functions in db and if function is missing or hash code is different then evals new version to declare/redeclare.

# select pg_install_library('http://github.com/hello/world');
Improve this question
7
  • PL/SQL is only used by Oracle. It's not available in (standard/plain) Postgres. To "install" a stored procedure, you run a create procedure statement. I think the concept you are looking for is provided through extensions in Postgres.
    – user1822
    May 5, 2022 at 15:02
  • postgres extension is hard to install. I have just access to psql session.
    – Daniil Iaitskov
    May 5, 2022 at 15:17
  • create extension can be used in psql - that doesn't really qualify as "hard" to me. But if you don't want to package your procedures into an extension, then just put them in SQL scripts and run those scripts. You might want to use a schema migration tool like Liquibase to automate that
    – user1822
    May 5, 2022 at 15:20
  • create extension expects local files at specific location to be ready to read. In cloud setup it is not feasible. The feature is not high level enough. Transitive dependencies are not handled, right?
    – Daniil Iaitskov
    May 5, 2022 at 15:24
  • You typically don't have a transitive dependencies for procedures. You don't include a gazillion of libraries as you do with npm. In fact you typically don't include any "libraries" in stored procedures or functions. You might have some utility functions or procedures that are re-used in others. But that is a simply matter of creating them in the right order. What is the underlying problem you are trying to solve? Which libraries do you intend to include here?
    – user1822
    May 6, 2022 at 5:29

1 Answer 1

Reset to default
1

The equivalent would be a PostgreSQL extension. Extensions have support for versioning and dependencies to other extensions.

Sure, you need shell access to the database machine to install the extension, just like with pip and other package managers.

Improve this answer

Your Answer

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

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