pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExecute SQL statements with 'context'/predefined variables
3+ messages / 2 participants
[nested] [flat]
* Execute SQL statements with 'context'/predefined variables
@ 2007-09-03 18:02 blay bloo <blaybloo@googlemail.com>
0 siblings, 1 reply; 3+ messages in thread
From: blay bloo @ 2007-09-03 18:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Execute SQL statements with 'context'/predefined variables
@ 2007-09-03 21:59 chester c young <chestercyoung@yahoo.com>
parent: blay bloo <blaybloo@googlemail.com>
0 siblings, 0 replies; 3+ messages in thread
From: chester c young @ 2007-09-03 21:59 UTC (permalink / raw)
To: blay bloo <blaybloo@googlemail.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Execute SQL statements with 'context'/predefined variables
@ 2007-09-04 04:08 chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: chester c young @ 2007-09-04 04:08 UTC (permalink / raw)
To: blay bloo <blaybloo@googlemail.com>; +Cc: pgsql-sql
...
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-09-04 04:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-03 18:02 Execute SQL statements with 'context'/predefined variables blay bloo <blaybloo@googlemail.com>
2007-09-03 21:59 ` chester c young <chestercyoung@yahoo.com>
2007-09-04 04:08 Re: Execute SQL statements with 'context'/predefined variables chester c young <chestercyoung@yahoo.com>
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