Обсуждение: 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

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.klaver@aklaver.com

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

-----------------------------------------------

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.klaver@aklaver.com

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.klaver@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.klaver@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.

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