On Jan 14, 2010, at 7:08 PM, Greg Smith wrote: > So more targeted examples like you're considering now would help.
Here's the trigger example which should help reveal some of the advantages of "native typing". This is a generic trigger that constructs and logs manipulation statements for simple replication purposes. The original plpython version is located here: http://ar.pycon.org/common/2009/talkdata/PyCon2009/020/plpython.txt [You'll need to scroll down to the very bottom of that page.] There are three points in this example that need to be highlighted: 1. There is no need for a "mogrify" function (see original in the above link). 2. Attributes/columns of the records (new/old) are extracted when referenced. 3. The comparisons in after_update uses the data type's actual inequality operator. The first point is true because "native typing" gives the user direct access to a given type's typoutput via ``str(ob)``. This makes constructing the PG string representation of a given object *much* easier--quote_nullable, and done. The original plpython example will need to be updated to compensate for any changes in conversion: arrays will now need special handling and MD arrays will not work at all. It also relies heavily on the Python object representation matching PG's; where that fails, special cases need to be implemented(composites, notably). All of that compensation performed in the original version is unnecessary in the plpython3 version. The second point touches on the "efficiency" that was referenced in an earlier message. No cycles are spent converting the contents of a container object unless the user chooses to. Naturally, there is no advantage performance-wise if you are always converting everything. I'd wager that with triggers, it's rare that everything needs to be converted. The third point reveals that Postgres.Object instances--a component of native typing--use the data type's operator for inequality. It's not limited to comparisons as all available Python operators are mapped to corresponding operators in PG. For many or all primitives, there is no added value over conversion. However, this provides a lot of convenience when working with UDTs, datetime types, and geometric types. ...ISTM that the primary advantage of "native typing" is that we get to define the Python interface to a given Postgres data type. Three files are attached: afterlog.py - the trigger returning function afterlog.sql - the sql exercising the TRF (creates the replica_log table as well) afterlog.out - the contents of the replica_log table after executing afterlog.sql To replay: \i afterlog.py \i afterlog.sql SELECT * FROM replica_log;
CREATE OR REPLACE FUNCTION log_manipulation()
RETURNS TRIGGER LANGUAGE plpython3u AS
$python$
from Postgres import quote_nullable, quote_ident, notify
# parameter type extracted from the statement
record_manipulation = prepare("INSERT INTO replica_log (sql) VALUES ($1)")
def log(sql):
record_manipulation(sql)
# notify listeners that new data is available
notify('replicas')
fmt_insert = "INSERT INTO {relname} ({columns}) VALUES ({values});".format
fmt_update = "UPDATE {relname} SET {changes} WHERE {keys};".format
fmt_delete = "DELETE FROM {relname} WHERE {keys};".format
def fmt_eqs(joinwith, keys, record, fmt = "{0} = {1}".format):
pairs = [fmt(quote_ident(k), quote_nullable(record[k])) for k in keys]
return joinwith.join(pairs)
##
# entry points
def after_insert(td, new):
relname = td.args[0]
sql = fmt_insert(
relname = relname,
columns = ', '.join(map(quote_ident, new.keys())),
values = ', '.join(map(quote_nullable, new.values())),
)
log(sql)
def after_update(td, old, new):
relname, *pkeys = td.args
modified_columns = {k : v for k,v in new.items() if old[k] != v}
# only log if there were modified columns
if modified_columns:
sql = fmt_update(
relname = relname,
changes = fmt_eqs(', ', modified_columns.keys(), modified_columns),
keys = fmt_eqs(' AND ', pkeys, old)
)
log(sql)
def after_delete(td, old):
relname, *pkeys = td.args
sql = fmt_delete(
relname = relname,
keys = fmt_eqs(' AND ', pkeys, old)
)
log(sql)
$python$;
afterlog.sql
Description: Binary data
afterlog.out
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
