Re: 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 <
pj(at)illuminatedcomputing(dot)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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.