How Can I Compile Postgresql (pl/pgsql) Functions from Sublime?

Question

Is there a way to compile pl/psql functions from within Sublime Text 2?

Answer 1

Add New Build System file with this commands and save it.

{
"cmd": ["psql", "-d", "your-database name",
"-U", "postgres", 
"-f", "$file"],
"word_wrap": "false"
}

Answer 2

Yes you can. In order for this answer to work your network user must have access to the database. The way to do it is to create a new build system in Sublime for postgresql. You can do this by clicking Tools>Build System>New Build System.... Then replace the default build text with:

{
  "path": "C:/Program Files (x86)/pgAdmin III/1.20/",
  "cmd": ["psql.exe", "-f", "$file", "postgresql://db-staging-1:5432/mydbname"],
  "selector": "source.postgresql",
  "shell": true
}

Path: This should be the location of your psql.exe executable. Note if this path is in your environment variables path this line is unnecessary.

CMD: This is what will be run from the command line. I've included my connection information here as well. You'll need to replace it with the server path and port number for your database. Note, if you are having trouble with getting your build to run, the easiest way to debug what it's actually trying to run is to add echo on the front of this line:

 "cmd": ["echo", "psql.exe", "-f", "$file", "postgresql://db-staging-1:5432/mydbname"],

Now the output of your build will be exactly what it's trying to run on the command line. If what it outputs here doesn't work on your command line then you need to change it to something that will.

Selector: This set the default build for postgresql files.

Shell: Treats the command as a shell script.

Now you can choose your build to be postgresql under Tools>Build System. After that a simple Ctrl+B will compile pl/pgsql functions to your database! Note that regular SQL can be run against your Postgresql database now as well.

If you regularly interact with more than one database at once, see this article as a good reference for setting up connections to multiple databases: How to make build system for PostgreSQL

Other Sublime text build options can be found here: http://sublimetext.info/docs/en/reference/build_systems.html

Answer 3

I actually just released a plugin (called DB1) that allows you to do just that. You can dynamically connect to and execute queries and functions against a PostgreSQL or MySQL database (I'm in the process of adding more databases too). A cool part about it is it doesn't require you to install anything on your computer (other than Sublime Text).

All you have to do is install DB1 through Package Control, and then in a view you can run the command DB1: Connect to connect to your database. You can then execute sql in that view through one of the DB1: Execute commands.

You can also just open the PSQL function (if you have it saved in a file) and execute the whole file.

To see how it works you can check out the DB1 Website or the documentation. Let me know if you have any questions about it!