Scott Marlowe wrote:
> 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.
(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
