From: blay bloo <blaybloo@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Execute SQL statements with 'context'/predefined variables
Date: Mon, 3 Sep 2007 19:02:18 +0100
Message-ID: <66c468170709031102j1e21d313v512c488ccc289505@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: blaybloo@googlemail.com
Subject: Re: Execute SQL statements with 'context'/predefined variables
In-Reply-To: <66c468170709031102j1e21d313v512c488ccc289505@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox