Thread: Stored procedure workflow question
Attachment
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
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 <pj@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.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.
--
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.