DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=Sz3MyDIxmGJrENXSqvQpbLShS2Ov8i5UdKG3ZMSaMa1bQSnXCMn4hsHPoz0GiBJdsz8IfJLGdEOx2JHILPwD4rXrLjXoJs8CUupXmd8ewtcBGrKXBIS4EGKA9pZ1WhP1XcjeQXVtBPLVCmD/6Po7yJgmMBsevBgC3dodWtLXgdU=
Message-ID: <66c468170709031102j1e21d313v512c488ccc289505@mail.gmail.com>
Date: Mon, 3 Sep 2007 19:02:18 +0100
From: "blay bloo" <blaybloo@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Execute SQL statements with 'context'/predefined variables
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I was wondering if it is possible to set the 'context' for running an
sql command:

For example, say you have a rule which upon update to table TEST of
type [a int, b varchar(10), c varchar(50)]:

INSERT INTO Log(a * 5, substring(b,0,2), randomize(c))

So an update of (5, 'hello', 'world') will add a row (25, 'he',
'##aa2') into the Log table.

Here we can perform operations and functions on the values of the
updated row, as the query engine has reference to the
(fieldname,value) pairs from the update. That is, the engine knows
that a=5, b='hello', etc.

My question - can you setup a similar 'context' for a statement
executed from python..

I.e. Let's say we have a python function, which has a name/value pair
tuple equivilent to the updated row
Is it possible to use the SQL parser to perform the operation in the
same manner?

I.e. from a plpython function can we do a:
plpy.execute("INSERT INTO Log(a * 5, substring(b,1,5), randomize(c))")
	-so that the db engine executing the insert knows that a=5, b='hello'
and c='world',
	-and thus perform operations on them

Clearly, we can have python excute the functions, and execute a string
with the values (function results). Unfortunately I can't (easily) do
this, as the 'insert' definition functions are passed defined as
strings, in which case I'd have to write a parser to work out what are
the functions, operations, variable names, etc.

It would be great to leverage from the SQL engine to handle this task,
which I can easily do so long as I can pass some variables to the db
before executing the command...

I guess bottom line, is it possible to execute a bunch of SQL
statements with some predefined variables?

I'm sure you're wondering why not just do it ALL in SQL. Kinda
complicated, so I might stall on that explanation if possible.

Thanks for your help!

Blay