Thread: Execute SQL statements with 'context'/predefined variables

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
thata=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

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

Oracle has a Context('varname') that returns the value of varname for
the session.  to best of my knowledge pg has nothing like this.

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

you could use rules to inject you constants as long as you were dealing
with known tables/views; you'd have to rewrite the rules each time the
constants changed; the rules are not session oriented - each sessions
would use the same rule.

(if you have not used pg rules imho you are in for a very pleasant
surprise.)


     ____________________________________________________________________________________
Luggage? GPS? Comic books? 
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz

...

> When you say use rules to inject constants, how would I go about
> doing this? Could you maybe give a brief example?

create view tab1_dml as select * from tab1;

-- note:
-- CONSTANT1 = 8
-- CONSTANT2 = 15

create or replace rule tab1_insert as  on insert  to tab1_dml
do instead(  insert into tab1( c1, c2 )  values( new.c1 * 8, 15 );
);


      
____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC