On Nov 21, 2007 12:21 PM, David Fetter <david@fetter.org> wrote:
>
> On Wed, Nov 21, 2007 at 11:10:15AM -0600, Scott Marlowe wrote:
> > On Nov 21, 2007 10:49 AM, Richard Huxton <dev@archonet.com> wrote:
> > > Gauthier, Dave wrote:
> > > > APparently, from "man psql", -c can do only one thing at a time. But you could do this with 2-3 commands (or 1
ifyou want to wrap the 2 up in a shell script or something). Here's an example...
> > > [snip]
> > > > psql --dbname mydb -c "\i create_try.sql;"
> > > > psql --dbname mydb -c "select trythis('foo');"
> > > > psql --dbname mydb -c "drop function trythis(varchar);"
> > >
> > > Or just put everything in one file and use -f <filename>
> >
> > And from the more than one way to skin a cat department:
> >
> > cat my.sql | psql mydb
> > psql mydb < my.sql
>
> Should anything go wrong with either of these constructs, you don't
> get the line number where it did, so the following is better:
Umm, as I posted before, I DO get the line number. the output I get
looks exactly the same as if I use -f.
Richard posted an example of when he did get the same thing, but not
one of where he didn't.
> psql -1 -f my.sql
>
> This ensures that the entire thing is run in one transaction, and when
> anything goes wrong, you'll know the line number where it did.
>
> Transactional DDL invaluable for changing schemas :)
That's handy, but I generally put the begin; commit; pair in my .sql
file anyway. I'm a huge fan of transactional DDL.
