Обсуждение: Database connectivity using a unix shell

> I need to connect to the postgres database using my unix shell.
> How should i go about it?
> Is libpq going to be helpful here?

If postgres is install on the unix server, you can use the postgreSQL client that installs with
the server.

it is called psql.

Regards,

Richard Broersma Jr.

On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:
> Hi
> I need to connect to the postgres database using my unix shell.
> How should i go about it?
> Is libpq going to be helpful here?

There is a shell interface for postgresql called psql.

If you have postgresql installed on a box, then the psql interface
should be there.

If you need to access postgresql from within a bash script, you can do
so with a construct kinda like this:

query="select * from sometable";
a=`echo $query|psql -tq dbname`;

do a psql --help to get a list of options for psql.  -t means print only
the rows, no headers, and -q means to only display query output, no
messages or warnings.

On Thu, 2006-06-29 at 11:08, Jasbinder Bali wrote:
>
> On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
>         On Thu, 2006-06-29 at 10:24, Jasbinder Bali wrote:
> > > Hi
> > > I need to connect to the postgres database using my unix shell.
> > > How should i go about it?
> > > Is libpq going to be helpful here?
>
> > There is a shell interface for postgresql called psql.
>
> > If you have postgresql installed on a box, then the psql interface
> > should be there.
>
> > If you need to access postgresql from within a bash script, you can
> > do So with a construct kinda like this:
>
> > query="select * from sometable";
> > a=`echo $query|psql -tq dbname`;

> in my bash script where and how do i specify the database connection
> parameters

In psql you can use -U to specify your username.  for password info, you
need to use a .pgpass file (i.e. no passwords on the command line).

http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html

On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
> isn't my normal bash script different from psql.
> In a bash script how wud u specify the db parameters

Look at how I'm doing it here:

>         > > query="select * from sometable";
>         > > a=`echo $query|psql -tq dbname`;

Note that I'm calling psql from within a bash script. So, the connection
params are the same as for psql, cause that's what I'm using.

On Thursday 29 June 2006 09:08, "Jasbinder Bali" <jsbali@gmail.com> wrote:
> in my bash script where and how do i specify the database connection
> parameters

man psql

--
Alan

> isn't my normal bash script different from psql.
> In a bash script how wud u specify the db parameters

This link might have something that you would like?
http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

Regards,

Richard Broersma Jr.

This is a variation of the same:

a=`psql -U postgres -h my.host.dom -Atc "select col1 from sometable where col2=6"  dbname`
echo $a

Note that the -U and -h can specify the user name and hostname if necessary.
-A gets the data unaligned
-t gets the data without the decorations
-c "query" specifies the query to run
dbname is required if it is not the default.

Also see psql --help

elein

On Thu, Jun 29, 2006 at 11:40:49AM -0500, Scott Marlowe wrote:
> On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
> > isn't my normal bash script different from psql.
> > In a bash script how wud u specify the db parameters
>
> Look at how I'm doing it here:
>
> >         > > query="select * from sometable";
> >         > > a=`echo $query|psql -tq dbname`;
>
> Note that I'm calling psql from within a bash script. So, the connection
> params are the same as for psql, cause that's what I'm using.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

jsbali@gmail.com ("Jasbinder Bali") writes:
> in my bash script where and how do i specify the database connection
> parameters

There are three ways:

1.  You can store them in environment variables:

PGDATABASE=my_database
PGPORT=8901
PGUSER=superman
PGHOST=some.host.somewhere
export PGDATABASE PGPORT PGUSER PGHOST

2.  You can specify them in command line parameters:

psql -h some.host.somewhere -p 8901 -U superman -d my_database

3.  You could specify a service name

PGSERVICE=my_fave_db psql

where the pg_service.conf in your PostgreSQL installation contains an
entry:

[my_fave_db]
dbname=my_database
user=superman
host=some.host.somewhere
port=8901

4.  You can mix these; perhaps start by having PGSERVICE provide part
of the information, then environment variables may add to/override
some of that, then command line parameters may add to/override
further...

For a lot of our production environments, we have set up a big,
comprehensive pg_service.conf file; local accounts then pick favorite
values for PGSERVICE...
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/lsf.html
"Why use Windows, since there is a door?"
-- <fachat@galileo.rhein-neckar.de> Andre Fachat

Are you just asking random questions? What do you actually want to do? You've
asked how to access Postres from a shell - now you're using 'C'. Are you going
to work your way through Java, Perl and a host of others.

All of this information is *very* clearly available in the manual at:
  http://www.postgresql.org/docs/8.1/interactive/index.html

It is an *excellent* manual. Apart from how to access Postgres in a multitude of
ways it includes a good reference on SQL itself.

Go have a read.

Jasbinder Bali wrote:
> What if I don't have a shell script and instead of that I have a C code
> and need to connect to the postgres database.
> How do i accomplish this? do i still need to call this psql clinet
> interface or there is some other way to do it..
>
> ~Jas
>
> On 6/29/06, *Scott Marlowe* <smarlowe@g2switchworks.com
> <mailto:smarlowe@g2switchworks.com>> wrote:
>
>     On Thu, 2006-06-29 at 13:29, Jasbinder Bali wrote:
>     > this is what i've included in my shell script
>     >
>     > query="select * from films";
>     > a=`echo $query|psql -tq postgres`;
>     >
>     >
>     > it gives an error:
>     > role root doesn't exist.
>     >
>     > where do i have to specify the role?
>
>     OK, unless this script NEEDS to be run as root, it's a good idea to run
>     it as an unprivaledged account.  Create a new one if you can.  If it
>     has
>     to be run as root, so be it.
>
>     You use -U, so it'd look like:
>
>     a=`echo $query|psql -tqU myname postgres`;
>
>     then you need a password in ~/.pgpass following the format I posted in
>     the previous message, unless your server is running in trust mode, in
>     which case you don't need to worry about the .pgpass file.
>
>     >
>     > On 6/29/06, Scott Marlowe <smarlowe@g2switchworks.com
>     <mailto:smarlowe@g2switchworks.com>> wrote:
>     >         On Thu, 2006-06-29 at 11:29, Jasbinder Bali wrote:
>     >         > isn't my normal bash script different from psql.
>     >         > In a bash script how wud u specify the db parameters
>     >
>     >         Look at how I'm doing it here:
>     >
>     >         >         > > query="select * from sometable";
>     >         >         > > a=`echo $query|psql -tq dbname`;
>     >
>     >         Note that I'm calling psql from within a bash script. So, the
>     >         connection
>     >         params are the same as for psql, cause that's what I'm using.
>     >
>
>

> What if I don't have a shell script and instead of that I have a C code and
> need to connect to the postgres database.
> How do i accomplish this? do i still need to call this psql clinet interface
> or there is some other way to do it..

In this case you could use ecpg:
http://www.postgresql.org/docs/8.1/interactive/ecpg.html

If you are interested in any other programming languages that can connect please see:
http://www.postgresql.org/download/
notice the section:
"Application Programming Interfaces"

Regards,

Richard Broersma Jr.

On Wednesday 05 July 2006 09:11, Jasbinder Bali wrote:
> Hi,
> Before using ECPG, i had a few questions.
>
> Lets say i have a C code and want to do something with my postgres
> database.
>
> What all header files do i need for ECPG.
> Also how and where exactly am i going to write these EXEC SQL statements
> for ECPG.
> Can I be briefed about the steps to do this.


http://www.postgresql.org/docs/8.1/static/server-programming.html

Sincerely,

Joshua D. Drake

> On Wednesday 05 July 2006 09:11, Jasbinder Bali wrote:
> > Hi,
> > Before using ECPG, i had a few questions.
> > Lets say i have a C code and want to do something with my postgres
> > database.
> > What all header files do i need for ECPG.
> > Also how and where exactly am i going to write these EXEC SQL statements
> > for ECPG.
> > Can I be briefed about the steps to do this.
> http://www.postgresql.org/docs/8.1/static/server-programming.html

Joshua,

The original posting was in regard to client side script/programs that a user would connect to a
database.  Doesn't the above link have application to client side connections?

Regards,

Richard Broersma Jr.

On Wednesday 05 July 2006 10:29, Richard Broersma Jr wrote:
> > On Wednesday 05 July 2006 09:11, Jasbinder Bali wrote:
> > > Hi,
> > > Before using ECPG, i had a few questions.
> > > Lets say i have a C code and want to do something with my postgres
> > > database.
> > > What all header files do i need for ECPG.
> > > Also how and where exactly am i going to write these EXEC SQL
> > > statements for ECPG.
> > > Can I be briefed about the steps to do this.
> >
> > http://www.postgresql.org/docs/8.1/static/server-programming.html
>
> Joshua,
>
> The original posting was in regard to client side script/programs that a
> user would connect to a database.  Doesn't the above link have application
> to client side connections?

Yep, you are right. Here is a better link:

http://www.postgresql.org/docs/8.1/static/client-interfaces.html

Sincerely,

Joshua D. Drake


>
> Regards,
>
> Richard Broersma Jr.

--
   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/