Re: loading a funtion script from a file

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