Обсуждение: 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.
> -----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, pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses containedin 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