Thread: How to query pgsql from a BASH script ?
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)
Attachment
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
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 ?
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)
Attachment
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)
Attachment
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
> 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
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.
Attachment
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)
Attachment
> > 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
# aklaver@comcast.net / 2005-04-03 11:14:54 -0700: > On Sunday 03 April 2005 07:16 am, Michelle Konzack wrote: > > 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/ 0.7.2 contains a few (potential) null pointer bugs, fixes should be in 0.7.3. ITMT, you can find them here: http://codex.sigpipe.cz/FreeBSD/ports/databases/shellsql-tools/files/ -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991
I have been trying to write an sql statement that returns the same hours in a time stamp no matter what the date. I can to pull same hours on the the same days but have not been able to figure out how to pull all the same hours no matter what the date. Here is the one sql statement I have been using: SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN 20050629100000 and 20050631100000; Any help would be appreciated. Thanks in advanced for any help -- "Well then what am I supposed to do with all my creative ideas- take a bath and wash myself with them? 'Cause that is whatsoap is for" (Peter, Family Guy)
D A GERM wrote:
> I have been trying to write an sql statement that returns the same
> hours in a time stamp no matter what the date.
> I can to pull same hours on the the same days but have not been able
> to figure out how to pull all the same hours no matter what the date.
>
> Here is the one sql statement I have been using:
> SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN
> 20050629100000 and 20050631100000;
>
> Any help would be appreciated.
>
> Thanks in advanced for any help
>
You can do something like
SELECT count(*)
FROM table
where date_part('hour', timestamp) in (10, 11)
This query is going to require a seq scan, so if you're running it
frequently you can make an index on date_part('hour', timestamp)
Jeff
On Wed, 2005-06-29 at 13:20, Jeffrey Melloy wrote:
> D A GERM wrote:
>
> > I have been trying to write an sql statement that returns the same
> > hours in a time stamp no matter what the date.
> > I can to pull same hours on the the same days but have not been able
> > to figure out how to pull all the same hours no matter what the date.
> >
> > Here is the one sql statement I have been using:
> > SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN
> > 20050629100000 and 20050631100000;
> >
> > Any help would be appreciated.
> >
> > Thanks in advanced for any help
> >
> You can do something like
> SELECT count(*)
> FROM table
> where date_part('hour', timestamp) in (10, 11)
>
> This query is going to require a seq scan, so if you're running it
> frequently you can make an index on date_part('hour', timestamp)
Note that an index created on date_part('hour',timestamp) should be
usable here as long as it's selectable enough.
thank you all for your help.
this solved it:
SELECT count(*) FROM table where date_part('hour', time_stamp) in (10, 11);
Scott Marlowe wrote:
>On Wed, 2005-06-29 at 13:20, Jeffrey Melloy wrote:
>
>
>>D A GERM wrote:
>>
>>
>>
>>>I have been trying to write an sql statement that returns the same
>>>hours in a time stamp no matter what the date.
>>>I can to pull same hours on the the same days but have not been able
>>>to figure out how to pull all the same hours no matter what the date.
>>>
>>>Here is the one sql statement I have been using:
>>>SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN
>>>20050629100000 and 20050631100000;
>>>
>>>Any help would be appreciated.
>>>
>>>Thanks in advanced for any help
>>>
>>>
>>>
>>You can do something like
>>SELECT count(*)
>>FROM table
>>where date_part('hour', timestamp) in (10, 11)
>>
>>This query is going to require a seq scan, so if you're running it
>>frequently you can make an index on date_part('hour', timestamp)
>>
>>
>
>
>Note that an index created on date_part('hour',timestamp) should be
>usable here as long as it's selectable enough.
>
>
--
D. Aaron Germ
Scarborough Library, Shepherd University
(304) 876-5423
"Well then what am I supposed to do with all my creative ideas- take a bath and wash myself with them? 'Cause that is
whatsoap is for" (Peter, Family Guy)