bash & postgres

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Greenhorn
Sent: Monday, 23 March 2009 3:03 PM
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org
Subject: [GENERAL] 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
#!/bin/bash
two="2"

Try something like

psql -h localhost -U postgres -d mobile <<ENDOFSQL
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;

ENDOFSQL

The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.

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

On Mon, 23 Mar 2009 15:03:15 +1100
Greenhorn <user.postgresql@gmail.com> 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 (

I enjoy another technique that's not exactly embedding but makes the
sql file easily executable from other shells to and easier to
maintain (eg. you don't lose syntax highlight, you don't need to
make wide use of x bit, you can concatenate files...).

echo "select :a;" | psql --set a=3 test
?column?
----------
3
(1 row)

of course in spite of piping your sql, you could put it into a file.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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