PGTS PGTS Pty. Ltd.   ACN: 007 008 568               Mobile Version Coming Soon

point Site Navigation

Valid HTML 4.01!

   Download Kubuntu Today


   The Power Of KDE + Ubuntu

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.


However, there seems to be some sort of upper limmit, say 250 or 300 pages

Amazing, FWIW,


Ed: Interesting little factoid. I suspect there are many such features
hidden away in the vast internals of Google.

Back To Index

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

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;
	 (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
	psql=# select :x from dual;
	(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

Here is an example that might be used to set a variable to a numeric

	psql=# \set x `perl -e 'print STDERR "Enter Number? ";$a=<>;print $a'`
	Enter Number? 596
	psql=# \echo :x
	psql=# select :x from dual;

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

	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:

	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;
	 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;
	 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?)

Back To Index

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

  * 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:

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

Back To Index