Running a query from the OS CLI
If I have a DB called "foo"
...and...
I want to run "select name from table_a where name like 'john%'"
...and...
I want no table header "NAME" in the output
...and...
I want to do this as a one-liner from the linux command line
...and...
I don't want to have to deal with intermediate files or home-grown
programs...
Is this possible?
See:
http://www.postgresql.org/docs/8.2/interactive/app-psql.html
________________________________
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gauthier, Dave
Sent: Wednesday, August 08, 2007 11:14 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Running a query from the OS CLI
If I have a DB called "foo"
-d dbname
--dbname dbname
Specifies the name of the database to connect to. This is equivalent to
specifying dbname as the first non-option argument on the command line.
<<
...and...
I want to run "select name from table_a where name like 'john%'"
-c command
--command command
Specifies that psql is to execute one command string, command, and then
exit. This is useful in shell scripts.
command must be either a command string that is completely parsable by
the server (i.e., it contains no psql specific features), or a single
backslash command. Thus you cannot mix SQL and psql meta-commands with
this option. To achieve that, you could pipe the string into psql, like
this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator
meta-command.)
If the command string contains multiple SQL commands, they are processed
in a single transaction, unless there are explicit BEGIN/COMMIT commands
included in the string to divide it into multiple transactions. This is
different from the behavior when the same string is fed to psql's
standard input.
<<
...and...
I want no table header "NAME" in the output
-t
--tuples-only
Turn off printing of column names and result row count footers, etc.
This is equivalent to the \t command.
<<
...and...
I want to do this as a one-liner from the linux command line
...and...
I don't want to have to deal with intermediate files or home-grown
programs...
Is this possible?
Read The Fine Manual.
<<
On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote:
If I have a DB called "foo"
psql --dbname "foo"
...and...
I want to run "select name from table_a where name like 'john%'"
psql --dbname "foo" -c "select name from table_a where name like 'john
%'"
...and...
I want no table header "NAME" in the output
psql --dbname "foo" -c "select name from table_a where name like 'john
%'" -t
...and...
I want to do this as a one-liner from the linux command line
check.
...and...
I don't want to have to deal with intermediate files or home-grown
programs...
Does that work?
Michael Glaesemann
grzm seespotcode net
On Aug 8, 2007, at 11:13 AM, Gauthier, Dave wrote:
If I have a DB called “foo”
...and...
I want to run “select name from table_a where name like ‘john%’”
...and...
I want no table header “NAME” in the output
...and...
I want to do this as a one-liner from the linux command line
...and...
I don’t want to have to deal with intermediate files or home-grown
programs...
Something like this:
psql -A -q -t -d foo -c “select name from table_a where name like
‘john%’”
You may need to use -U to set a user, and there are a bunch of other
useful flags to set the output format. There are also flags and
environment
variables you can set to set the host and port to connect to.
Depending on how your access control permissions are setup you may
need to get a password to psql, typically by using a ~/.pgpass file.
Check
the psql man page and the main postgresql docs for the gory details.
Cheers,
Steve
Yes, that works.
The actual sql executes a stored function that returns a set of records.
Output to the CLI looks great !!!
Thanks to all !!!
-dave
-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Wednesday, August 08, 2007 2:43 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Running a query from the OS CLI
On Aug 8, 2007, at 13:13 , Gauthier, Dave wrote:
If I have a DB called "foo"
psql --dbname "foo"
...and...
I want to run "select name from table_a where name like 'john%'"
psql --dbname "foo" -c "select name from table_a where name like 'john
%'"
...and...
I want no table header "NAME" in the output
psql --dbname "foo" -c "select name from table_a where name like 'john
%'" -t
...and...
I want to do this as a one-liner from the linux command line
check.
...and...
I don't want to have to deal with intermediate files or home-grown
programs...
Does that work?
Michael Glaesemann
grzm seespotcode net