Re: [GENERAL] Stored procedure workflow question
On 12/10/2014 6:53 PM, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- This is pretty much what I do as well. I keep all my scripts and check them into subversion though. I edit them with vim and use the dbext plugin to run snippets on the test db. Once I'm ready I run it on production. (Could be an entire file, or just a copy/paste of a block) I use psql for everything. (vim dbext uses psql as well) It doesn't seem that clunky though. You do a bunch of stuff on testdb, once you get it right you do it on livedb. I don't see any other ways of doing it. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stored procedure workflow question
Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following:- Create/edit the desired function in my "DB Commands" text file- Copy and paste function into my development database- Test- repeat above until it works as desired- Copy and paste function into my production DB.To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file.This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Stored procedure workflow question
On 11/12/14 13:53, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- I create an SQL file using a text editer, and then execute it in psql using the '\i' command from the appropriate directory: gavin= \i bus.sql I your case I would test it in one environment and copy it to another. You could use git to track versions of the file and the nature of changes. Though, I am sure there are sophisticated ways of doing this! Cheers, Gavin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored procedure workflow question
On 12/10/2014 04:53 PM, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. For above use \ef in psql. See here for more details: http://www.postgresql.org/docs/9.3/interactive/app-psql.html You can save the file outside the database if needed. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. Keep the functions in separate files. Do either: psql -d some_database -U some_user -f function_file.sql or from within psql \i some/path/to_file/function_file.sql --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored procedure workflow question
On 12/10/2014 05:53 PM, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- I don't quite follow cut and paste it into some database, but you method doesn't sound any more kludy than code-compile-test-install workflow in other coding environments. I will say emacs make this a snap, but I won't say how;)
Re: [GENERAL] Stored procedure workflow question
On 12/10/2014 05:03 PM, Gavin Flower wrote: On 11/12/14 13:53, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- I create an SQL file using a text editer, and then execute it in psql using the '\i' command from the appropriate directory: gavin= \i bus.sql I your case I would test it in one environment and copy it to another. You could use git to track versions of the file and the nature of changes. Though, I am sure there are sophisticated ways of doing this! Two that come to mind: Sqitch http://sqitch.org/ Alembic https://alembic.readthedocs.org/en/latest/ Cheers, Gavin -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored procedure workflow question
How do you handle DDL changes in general? I would treat stored procedures the same way. For instance Ruby on Rails has database migrations where you write one method to apply the DDL change and another to revert it, like this: def up add_column :employees, :manager_id, :integer add_index :employees, :manager_id end def down remove_column :employees, :manager_id end You could create stored procedures like: def up connection.execute -EOQ CREATE OR REPLACE FUNCTION ... EOQ end or even: def up connection.execute File.read(Rails.root + 'db' + 'procs' + 'my_function.sql') end That's how I'd do it in Rails. Maybe your development context has something similar? Paul On Wed, Dec 10, 2014 at 5:53 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 12/10/2014 05:03 PM, Gavin Flower wrote: On 11/12/14 13:53, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- I create an SQL file using a text editer, and then execute it in psql using the '\i' command from the appropriate directory: gavin= \i bus.sql I your case I would test it in one environment and copy it to another. You could use git to track versions of the file and the nature of changes. Though, I am sure there are sophisticated ways of doing this! Two that come to mind: Sqitch http://sqitch.org/ Alembic https://alembic.readthedocs.org/en/latest/ Cheers, Gavin -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Stored procedure workflow question
I suggest you download and install PgAdmin. http://www.pgadmin.org/index.php It makes review of functions and other database objects, as well as maintenance, a lot easier. Otherwise, you can just use psql eg: psql your_database \o /some_dir/your_proc_filename \sf+ your_proc \q Your function definition will now be in /your_proc_filename On Wed, Dec 10, 2014 at 10:07 PM, Paul Jungwirth p...@illuminatedcomputing.com wrote: How do you handle DDL changes in general? I would treat stored procedures the same way. For instance Ruby on Rails has database migrations where you write one method to apply the DDL change and another to revert it, like this: def up add_column :employees, :manager_id, :integer add_index :employees, :manager_id end def down remove_column :employees, :manager_id end You could create stored procedures like: def up connection.execute -EOQ CREATE OR REPLACE FUNCTION ... EOQ end or even: def up connection.execute File.read(Rails.root + 'db' + 'procs' + 'my_function.sql') end That's how I'd do it in Rails. Maybe your development context has something similar? Paul On Wed, Dec 10, 2014 at 5:53 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 12/10/2014 05:03 PM, Gavin Flower wrote: On 11/12/14 13:53, Israel Brewster wrote: Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following: - Create/edit the desired function in my DB Commands text file - Copy and paste function into my development database - Test - repeat above until it works as desired - Copy and paste function into my production DB. To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file. This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- I create an SQL file using a text editer, and then execute it in psql using the '\i' command from the appropriate directory: gavin= \i bus.sql I your case I would test it in one environment and copy it to another. You could use git to track versions of the file and the nature of changes. Though, I am sure there are sophisticated ways of doing this! Two that come to mind: Sqitch http://sqitch.org/ Alembic https://alembic.readthedocs.org/en/latest/ Cheers, Gavin -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
