Site Navigation
Feedback: September 2003, Published: October 2003
Not much to report this month. I have just returned from a holiday. Spam is now being logged in the new Spam reporting system.Hints for this month:
New Discovery About Google
Date: Wed, 24 Sep 2003 15:18:59 +1000 (EST) From: Brian Robson Dear reader, If you go to google, and type in allinurl: and then a web site, you will get everything on the site, as found by google. Initially it will just give a couple of pages, repeat the search, asking to show all. Examples.... allinurl:www.danbyrnes.com.au/ allinurl:www.sharoncolon.com/ However, there seems to be some sort of upper limmit, say 250 or 300 pages maximum. Amazing, FWIW, Brian Ed: Interesting little factoid. I suspect there are many such features hidden away in the vast internals of Google.
What is the PostgreSQL equivalent of the oracle ACCEPT
Date: Tue, 30 Sep 2003 01:45:06 +1000 From: Gerry Patterson Well actually there isn't one. One of the most annoying things about postgres is the manner in which single quotes are handled. Still a little experimentation proved that it was possible to do something like this: psql=# \set x blah psql=# \echo :x blah Where "psql=#" is the psql prompt and the plain text is the output. However, if you want to use the variable :x in an SQL expression you, would need to put it into quotes. My first attempts at this were frustating. Putting something in single quotes prevents it being evaluated. So when I tried the following: psql=# select ':x' from dual; ?column? ---------- :x (1 row) Which was not what I wanted. It seems that if you want to use the variable :x as a string literal the quotes must be included when it is set with psql \set command. i.e.: psql=# \set x '\'blah\'' psql=# \echo :x 'blah' psql=# select :x from dual; ?column? ---------- blah (1 row) Which assumes that you are so enamoured to the oracle table dual, that you created an equivalent in postgres. But how is it possible to put this into effect, with a simple perl one-liner? Here is an example that might be used to set a variable to a numeric value: psql=# \set x `perl -e 'print STDERR "Enter Number? ";$a=<>;print $a'` Enter Number? 596 psql=# \echo :x 596 psql=# select :x from dual; ?column? ---------- 596 The variable :x can now be used in an SQL query. Note that the message must be put to STDERR (because psql will snabble anything sent to STDOUT). All of which is just fine and dandy for numbers ... But what to do about those damned elusive single quotes? The problem is of course, that we need the single quotes around the one-liner. Just about any solution will be messy. Whatever I came up with seemed too convoluted to be a one-liner. The following might be a possibility: psql=# \set x `perl -e 'print STDERR "Enter string? ";chomp($a=<>);print "\x27$a\x27"'` Which is almost too long for a one-liner. A simpler solution might entail creating a small script like the following: #!/usr/bin/perl print STDERR "Enter @ARGV? "; $a = <STDIN>; chomp $a; print "'$a'"; This was saved in a file called "getstr" and placed in the PATH. Now the following command will return the quoted literal: psql=# \set x `getstr some string` Enter some string? blah blah psql=# select :x from dual; ?column? ----------- blah blah (1 row) With this program file in place the command to set a variable to a literal is: \set x `getstr some text` This will set :x to a string enclosed in single quotes. The words some text can be anything. The command will print "Enter some text? ". If you put "a load of old rope" it would be "Enter a load of old rope? ". Now, if you want to include a single quote in your variable you must use the usual method. For example: psql=# \set reply `getstr text with apostrophe` Enter text with apostrophe? That''s a wrap psql=# select :reply from dual; ?column? --------------- That's a wrap (1 row) The long (one-liner) version might be appropriate for an SQL script that is being sent offsite (assuming the site has perl -- surely there couldn't be many sites without perl?)
What are the connectOptions for pg_connect?
Date: Tue, 30 Sep 2003 23:57:30 +1000
From: Gerry Patterson
After persisting with perl for almost two years, I have relented and
started to experiment with pgtclsh. The postgres support for tcl seems
to be better than for any other scripting language. Actually tcl is very
easy to learn (especially if you have a couple of decades of C
programming, and scripting with shell, awk, sed etc).
Whilst using the pg_connect option, I found the helpful hint that when
using the -conninfo option the connectOptions are documented in libpq's
PQconnectdb() manual entry. But where is that entry? I couldn't find it
anywhere. Here are some options that I googled:
* host -- host to connect to. If a non-zero-length string is
specified, TCP/IP communication is used. Without a host name, libpq
will connect using a local Unix domain socket.
* port -- port number to connect to at the server host, or socket
filename extension for Unix-domain connections.
* dbname -- database name.
* user -- user name for authentication.
* password -- password used if the backend demands password
authentication.
* authtype -- authorization type. (No longer used, since the backend
now chooses how to authenticate users. libpq still accepts and
ignores this keyword for backward compatibility.)
* options -- trace/debug options to send to backend.
* tty -- file or tty for optional debug output from backend.
This seems to work ok ...
So, something like the following gets you connected:
#!/usr/pgsql/bin/pgtclsh
set testDB [pg_connect -conninfo "host=bighost dbname=test user=scott password=tiger"]
(provided, of course, that your pg_hba.conf has been setup to allow this
connection)