Обсуждение: bash & postgres

Hi,

I'm trying to pass variables on a bash script embedded with psql commands.

cat header.txt

"to1","from1","subject1"
"to2","from2","subject2"
"to3","from3","subject3"
"to4","from4","subject4"

cat b.sh

#!/bin/bash
two="2"

psql -h localhost -U postgres -d mobile -c "create temp table header (

 field_1   text    not null,
 field_2   text    not null,
 field_3   text    not null

);

\\copy header FROM header.txt CSV

SELECT * FROM header limit "$two"; "


When I execute b.sh
================================
ERROR:  syntax error at or near "\"
LINE 10: \copy header FROM header.txt CSV
         ^

How do I use \c (or any other psql commands beginning with a "\") in a
bash script?

Thanks.

On Mar 22, 2009, at 9:03 PM, Greenhorn wrote:

> Hi,
>
> I'm trying to pass variables on a bash script embedded with psql
> commands.
>
> cat header.txt
>
> "to1","from1","subject1"
> "to2","from2","subject2"
> "to3","from3","subject3"
> "to4","from4","subject4"
>
> cat b.sh
>
> #!/bin/bash
> two="2"
>
> psql -h localhost -U postgres -d mobile -c "create temp table header (
>
> field_1   text    not null,
> field_2   text    not null,
> field_3   text    not null
>
> );
>
> \\copy header FROM header.txt CSV
>
> SELECT * FROM header limit "$two"; "
>
>
> When I execute b.sh
> ================================
> ERROR:  syntax error at or near "\"
> LINE 10: \copy header FROM header.txt CSV
>         ^
>
> How do I use \c (or any other psql commands beginning with a "\") in a
> bash script?

For multi-line input to a psql call in a bash (or any decent shell)
script, I'd use a here document:

#!/bin/bash

#!/bin/bash
two="2"

psql -d pagila <<COPYTEST
create temp table header (

field_1   text    not null,
field_2   text    not null,
field_3   text    not null

);

\copy header FROM header.txt CSV

SELECT * FROM header limit $two;
COPYTEST

$ ./tst.sh
Null display is "\N".
Timing is on.
CREATE TABLE
Time: 7.568 ms
Time: 2.374 ms
  field_1 | field_2 | field_3
---------+---------+----------
  to1     | from1   | subject1
  to2     | from2   | subject2
(2 rows)

Time: 1.011 ms

(P.S. Your quotes around $two in your original are not needed, in fact
they're straight up broken as $two is already inside of a double-
quoted string).

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Erik Jones <ejones@engineyard.com> writes:
> On Mar 22, 2009, at 9:03 PM, Greenhorn wrote:
>> How do I use \c (or any other psql commands beginning with a "\") in a
>> bash script?

> For multi-line input to a psql call in a bash (or any decent shell)
> script, I'd use a here document:

Or echo/cat the script into psql's stdin, if you prefer that type of
notation.  The reason you have to do this is that psql doesn't recognize
backslash commands in a -c string.  There's a school of thought that
doesn't want us to allow multiple commands in a -c string, even.

            regards, tom lane

On Mar 23, 2009, at 7:05 AM, Tom Lane wrote:

> Erik Jones <ejones@engineyard.com> writes:
>> On Mar 22, 2009, at 9:03 PM, Greenhorn wrote:
>>> How do I use \c (or any other psql commands beginning with a "\")
>>> in a
>>> bash script?
>
>> For multi-line input to a psql call in a bash (or any decent shell)
>> script, I'd use a here document:
>
> Or echo/cat the script into psql's stdin, if you prefer that type of
> notation.  The reason you have to do this is that psql doesn't
> recognize
> backslash commands in a -c string.  There's a school of thought that
> doesn't want us to allow multiple commands in a -c string, even.

Hmm... Apparently it does recognize them as long as the backslash is
the first character in the command string:

$ psql -c '\d' postgres
No relations found.
$ psql -c ' \d' postgres
ERROR:  syntax error at or near "\"
LINE 1:  \d
          ^

Is that expected behavior?

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

Erik Jones <ejones@engineyard.com> writes:
> On Mar 23, 2009, at 7:05 AM, Tom Lane wrote:
>> The reason you have to do this is that psql doesn't recognize
>> backslash commands in a -c string.  There's a school of thought that
>> doesn't want us to allow multiple commands in a -c string, even.

> Hmm... Apparently it does recognize them as long as the backslash is
> the first character in the command string:

Hmm, maybe I was just misremembering the details.  What's certainly
true is that psql doesn't run a -c string through the same extensive
parsing that data from stdin gets.

            regards, tom lane