SEO

Hello *,

I am puzzeling around, how to query a postgresql from a BASH script.
Generaly it must do nothing else as

1) Get VALUEs from a DB/TABLE
e.g.
program --db pgsql.sld.tld --search "$COLNAME,$VAL" \
--get "COL1,COL3,COL4,..."

2) Write one or more new VALUEs in the DB/TABLE
e.g.
program --db pgsql.sld.tld --search "$COLNAME,$VAL" \
--set "COL1:VAL1,COL3:VAL3,..."

3) Remove ROWS from the DB/TABLE
e.g.
program --db pgsql.sld.tld --search "$COLNAME,$VAL" --remove


Curently I have only a sulution with a text/plain file but
there is a problem with locking and the file is already
180 kByte, which mean grep/cut/sed are to slow for it.

Under "heavy" load I need to access the database around
1-3 times (maybe in the future more) per second.

Any suggestions ?

Greetings
Michelle

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSM LinuxMichi
0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCT/qvC0FPBMSS+BIRAh2oAKCIP3BYsAxFkDQFpGhEAqdNjs35VgCg tSiZ
ldrzTdusdjQ0Whmxm1q2QOE=
=xTCD
-----END PGP SIGNATURE-----

On Sun, 3 Apr 2005, Michelle Konzack wrote:

> Hello *,
>
> I am puzzeling around, how to query a postgresql from a BASH script.
> Generaly it must do nothing else as
>

Hello,

There is more solutions. The best is pgbash (pgbash is patch for bash)
http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

and last two months there somebody released new functions for bash and
postgresql but I can't remember

/usr/local/bin/pgbash
connect to testdb011;
BEGIN;
DECLARE c CURSOR FOR
SELECT name FROM names WHERE name LIKE '$1%';
lines=1
FETCH IN c INTO :name;
while [ $SQLCODE -eq $SQL_OK ]; do
if [ $lines -gt $2 ] ; then
break
fi
echo $name
let "lines+=1"
FETCH IN c INTO :name;
done
END;
disconnect all;

or

#!/usr/local/bin/pgbash
connect to template1;

set option_header=off;
set option_bottom=off;
set option_alignment=off;
set option_separator=;

dblist=`SELECT d.datname FROM
pg_catalog.pg_database d LEFT JOIN pg_catalog.pg_user u
ON d.datdba = u.usesysid WHERE u.usename LIKE '$1';`

if [ "$dblist" != "" ]; then
echo "$dblist" | while read db; do
echo "Remove database $db"
DROP DATABASE \"$db\";
done
fi
disconnect all;

regards
Pavel Stehule


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Michelle,

There may be other answers for this, but if you need to connect 2-3 times
per second, you probably need a persistent connection which, as far as I
know, can't be obtained (and maintained) from bash. Is there a reason not
to do this from the server side or even from a standard client-side language
like perl, java, or C?

Sean

----- Original Message -----
From: "Michelle Konzack" <linux4michelle@freenet.de>
To: <pgsql-general@postgresql.org>
Sent: Sunday, April 03, 2005 10:16 AM
Subject: [GENERAL] How to query pgsql from a BASH script ?




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Am 2005-04-03 10:41:06, schrieb Sean Davis:
> Michelle,
>
> There may be other answers for this, but if you need to connect 2-3 times
> per second, you probably need a persistent connection which, as far as I
> know, can't be obtained (and maintained) from bash. Is there a reason not
> to do this from the server side or even from a standard client-side
> language like perl, java, or C?

First: I have no clue about perl and java.
Second: I use C, but never used in conjunction with a database

I the datsbase should queried from, e.g., a procmailrc and check
for IP addresses and much more. Most TABLES has only 2 or 3 COLS

The problem are the ROWS of the database.

So I am searching for a simpel solution to access the DB from BASH

OK, I have already codes stuff in php and the RETVAL was simpel
"VAL1 VAL2 VAL3" which I can cut into:

RETVAL=`php pg_query.php $SERVER $DB $TABLE $COL $SEARCH`
if [ $? == "1" ] ; then exit 1 ; fi
VAL1=`echo $RETVAL |cut -d " " -f1`
VAL2=`echo $RETVAL |cut -d " " -f2`
VAL3=`echo $RETVAL |cut -d " " -f3`

where the pg_query.php has serched only in the
given $COL and returnd the whole $ROW as RETVAL.

I was thinkg that such tool already exist in C.

But if you have a DB of 10.000 lines, the above examle will be faster
then a BASH solution with a file like

RETVAL=`grep "^$SEARCH" $DB_FILE`
if [ $? == "1" ] ; then exit 1 ; fi
VAL1=`echo $RETVAL |cut -d " " -f1`
VAL2=`echo $RETVAL |cut -d " " -f2`
VAL3=`echo $RETVAL |cut -d " " -f3`

and pgsql can be updated concurently, a $DB_FILE not.

Oh yes, from time to time a have realy need for a DB even in stupid
BASH scripts because it makle the life easier.

And the other think is, the first $COL is every time UNIQ.

> Sean

Greetings
Michelle

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSM LinuxMichi
0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCUA0XC0FPBMSS+BIRAtfeAJ47HJRgYQ6bOpGBUfekat dU2G+mFgCeITVs
eohEusCzyaYmKrJD3v2jZA8=
=xq6g
-----END PGP SIGNATURE-----

Hello Pavel,

Am 2005-04-03 16:36:47, schrieb Pavel Stehule:

> Hello,
>
> There is more solutions. The best is pgbash (pgbash is patch for bash)
> http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html

Wow... realy cool.
Unfortunatly I can not get the bash-2.0.5a tarball - ServerError
I will try to apply the patch to the version in Debian WOODY...

> and last two months there somebody released new functions for bash and
> postgresql but I can't remember

<example>

I was reading the Website, FAO and Examples...
But some things are confusing.

I need only a simple returnvalue of a FULL_ROW (one String, where $FIELD
are seperated by whitespace) from a SEARCH in the first $COL (uniq).

So if I understand it right, if I have a table like

Table: ip_table
ip | ctime | atime
----------------+------------+------------
aaa.bbb.ccc.ddd | 1234567890 | 2345678901
eee.fff.ggg.hhh | 3456789012 | 4567890123

and I need only

CONNECT TO localhost USER michelle.konzack;
RETVAL=`SELECT ctime, atime FROM ip_table WHERE ip = $SEARCH;`

?

> regards
> Pavel Stehule

Greetings
Michelle

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSM LinuxMichi
0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCUCOEC0FPBMSS+BIRAsbLAJ9gkGsGMk9uauUoYU3vl5 jAA1TzbgCdGC0L
2+3QgzX8E4dpnNNoFwyHTtg=
=7MEG
-----END PGP SIGNATURE-----

On Sunday 03 April 2005 07:16 am, Michelle Konzack wrote:
> Hello *,
>
> I am puzzeling around, how to query a postgresql from a BASH script.
> Generaly it must do nothing else as
>
I recently came across this program-ShellSQL. I haven't had time to try it,
just read through the documentation. It may be able to do what you want.
http://www.edlsystems.com/shellsql/

--
Adrian Klaver
aklaver@comcast.net

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> So if I understand it right, if I have a table like
>
> Table: ip_table
> ip | ctime | atime
> ----------------+------------+------------
> aaa.bbb.ccc.ddd | 1234567890 | 2345678901
> eee.fff.ggg.hhh | 3456789012 | 4567890123
>
> and I need only
>
> CONNECT TO localhost USER michelle.konzack;
> RETVAL=`SELECT ctime, atime FROM ip_table WHERE ip = $SEARCH;`
>
test=# select * from foo;
a | b
----+----
10 | 10
10 | 20

pgbash> retval=`select a,b from foo;`
pgbash> echo $retval
a | b ----+---- 10 | 10 10 | 20 (2 rows)

I remeber other project

http://www.edlsystems.com/shellsql

bye

Pavel Stehule



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Sun, Apr 03, 2005 at 11:17:41PM +0200, Pavel Stehule wrote:
> test=# select * from foo;
> a | b
> ----+----
> 10 | 10
> 10 | 20
>
> pgbash> retval=`select a,b from foo;`
> pgbash> echo $retval
> a | b ----+---- 10 | 10 10 | 20 (2 rows)

The way I usually do it in scripts is:

psql '-F<tab>' -A -t -c "query"

If there's only one field output you can drop the -F.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFCUVWFY5Twig3Ge+YRAjVzAJ4wBiJSkTkBT2B82uf75u 9M7H36dgCg29ZJ
4whby4AoQ6oyE9cPkslxinE=
=0zMa
-----END PGP SIGNATURE-----

Am 2005-04-03 23:17:41, schrieb Pavel Stehule:

> I remeber other project
>
> http://www.edlsystems.com/shellsql

Thanks I will heck it out immediatly...

"pgbash" is a real killer but I can not apply
the patches becasue it ends in a error.

> bye
>
> Pavel Stehule

Greetings
Michelle

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
Michelle Konzack Apt. 917 ICQ #328449886
50, rue de Soultz MSM LinuxMichi
0033/3/88452356 67100 Strasbourg/France IRC #Debian (irc.icq.com)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFCUVsmC0FPBMSS+BIRAgedAKCGlskhiyFYPzHw2t23hG Gyn20u/ACfRKpW
/YYaklb+HUAQkwv+zGqAEJg=
=cCMB
-----END PGP SIGNATURE-----

>
> Thanks I will heck it out immediatly...
>
> "pgbash" is a real killer but I can not apply
> the patches becasue it ends in a error.
>

pgbash is nice, but without actualisation. You have to use good version of
bash,

[stehule@stehule stehule]$ pgbash --version
GNU bash, version 2.05a.0(1)-release (i586-pc-linux-gnu)
Copyright 2001 Free Software Foundation, Inc.

Pavel


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly