Scott Marlowe wrote:
On Nov 21, 2007 12:21 PM, David Fetter <david@xxxxxxxxxx> wrote:
On Wed, Nov 21, 2007 at 11:10:15AM -0600, Scott Marlowe wrote:
On Nov 21, 2007 10:49 AM, Richard Huxton <dev@xxxxxxxxxxxx> 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 if you 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.
(checks again). No, they're different:
$ psql82 -U richardh -f test1.sql
...
psql:test1.sql:12: ERROR: syntax error at or near "SELCT"
$ cat test1.sql | psql82 -U richardh
...
ERROR: syntax error at or near "SELCT"
The -f gives me line 12, from STDIN it doesn'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.
Hmm - didn't know the -1 thing. That's cool. I like to set ON_ERROR_STOP
too.
Almost as useful as BEGIN...COMMIT around schema changes is
BEGIN...ROLLBACK. I like to see it's all going to work before applying
the change. Of course, not always practical with changes to large tables.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
