class PyFunc(sql.SQLPyFunction):
name = 'times'
arguments = (
sql.Argument('x', pytis.data.Integer()),
sql.Argument('y', pytis.data.Integer()),
)
result_type = pytis.data.Integer()
stability = 'immutable'
class Util(sql.SQLPyFunction.Util):
INCREMENT = 1
@staticmethod
def pythonic(x, y):
return x * y
class Computer(object):
def compute(self, value):
return value + PyFunc.Util.INCREMENT
@staticmethod
def times(x, y):
computer = PyFunc.Util.Computer()
return computer.compute(PyFunc.Util.pythonic(x, y))
class Func(sql.SQLFunction):
name = 'plus'
arguments = (sql.Argument('x', pytis.data.Integer()), sql.Argument('y', pytis.data.Integer()),)
result_type = pytis.data.Integer()
stability = 'immutable'
def body(self):
return 'SELECT $1 + $2'
class FileFunc(sql.SQLFunction):
name = 'minus'
arguments = (
sql.Argument('x', pytis.data.Integer()),
sql.Argument('y', pytis.data.Integer()),
)
result_type = pytis.data.Integer()
stability = 'immutable'
class FileFuncOverloaded(sql.SQLFunction):
db_name = 'minus'
name = 'minus_float'
arguments = (
sql.Argument('x', pytis.data.Float()),
sql.Argument('y', pytis.data.Float()),
)
result_type = pytis.data.Integer()
stability = 'immutable'
class FUpdateCachedTables(sql.SQLPlFunction):
"""Trigger function to increase data versions of cached tables.
It increments version of both the given SCHEMA_.NAME_ table and
of all the dependent tables.
"""
name = 'f_update_cached_tables'
arguments = (
sql.Argument('schema_', pytis.data.String()),
sql.Argument('name_', pytis.data.String()),
sql.Argument('top_', pytis.data.Boolean()),
)
result_type = None
depends_on = (CachedTables, )
class RowFunction(sql.SQLPlFunction):
name = 'row_n'
arguments = (sql.Argument('row', Foo),)
result_type = pytis.data.Integer()
stability = 'immutable'
def body(self):
return "begin return row.n; end;"
class SideEffectFunction(sql.SQLPyFunction):
name = 'foo_insert'
arguments = (sql.Argument('n', pytis.data.Integer()),)
@staticmethod
def foo_insert(n):
plpy.execute("insert into foo (n) values (%s)" % (n,))
class EnableTriggers(sql.SQLPlFunction):
name = 'EnableTriggers'
arguments = (sql.Argument('Name', pytis.data.String()), )
result_type = pytis.data.Boolean()
@classmethod
def body(class_):
return """
DECLARE rel ALIAS FOR $1; rows INTEGER;
BEGIN
UPDATE pg_class SET reltriggers =
(SELECT Count(*) FROM pg_trigger
WHERE pg_class.oid = tgrelid)
WHERE relname ~~* rel;
GET DIAGNOSTICS rows = ROW_COUNT;
IF rows > 0 THEN
RETURN TRUE;
ELSE
RAISE NOTICE 'Relation does not exist';
RETURN False;
END IF;
END;
"""
depends_on = ()
class PyFuncSingleArg(sql.SQLPyFunction):
name = 'single_argument'
arguments = (sql.Argument('x', pytis.data.Integer()),)
result_type = pytis.data.Integer()
stability = 'immutable'
@staticmethod
def single_argument(x):
return x + 1
class MultilinePyArguments(sql.SQLPyFunction):
name = 'multilineargs'
arguments = tuple(
[sql.Argument(n, pytis.data.Integer()) for n in 'abcdefghi'])
result_type = pytis.data.Integer()
stability = 'immutable'
@staticmethod
def multilineargs(a, b, c, d, e, f, g, h, i):
return a + b + c + d + e + f + g + h + i
class TableSelectFunction(sql.SQLPyFunction):
name = 'tableselect'
schemas = (('public', Private,),)
arguments = (sql.Argument('foo', pytis.data.Integer()),)
result_type = Bar
multirow = True
stability = 'stable'
@staticmethod
def tableselect(foo):
return plpy.execute("select * from private.bar where foo_id >= %s" % (foo,))
class SelectFunc(sql.SQLFunction):
name = 'foo_increment'
arguments = (sql.Argument('inc', pytis.data.Integer()),)
result_type = pytis.data.Integer()
multirow = True
stability = 'stable'
def body(self):
return sqlalchemy.select([sql.c.Foo.n + sqlalchemy.literal_column('$1')],
from_obj=[sql.t.Foo])
depends_on = (Foo,)
class TableFunction(sql.SQLPyFunction):
name = 'pseudotable'
arguments = (sql.Argument('n', pytis.data.Integer()),)
result_type = (sql.Column('x', pytis.data.Integer()),
sql.Column('y', pytis.data.Integer()),
sql.Column('z', pytis.data.Integer()),)
multirow = True
stability = 'immutable'
@staticmethod
def pseudotable(n):
result = []
for i in range(1, n + 1):
for j in range(1, n + 1):
result.append([i, j, i * j])
return result
class RowSelectFunc(sql.SQLFunction):
name = 'row_select_func'
arguments = (sql.Argument('min_n', pytis.data.Integer()),)
result_type = (sql.Column('foo', pytis.data.String()),
sql.Column('n', pytis.data.Integer()),
sql.Column('b', pytis.data.Boolean()),
)
multirow = True
stability = 'stable'
execution_cost = 10
expected_rows = 4
def body(self):
foo = sql.t.Foo
return sqlalchemy.select(self._exclude(foo, foo.c.id, foo.c.description),
whereclause=(foo.c.n >= ival('$1')))
depends_on = (Foo,)
class FViewLog(sql.SQLPlFunction):
name = 'f_view_log'
arguments = (sql.Argument('date_from', pytis.data.Date()),
sql.Argument('date_to', pytis.data.Date()),
sql.Argument('username_', pytis.data.String()),
sql.Argument('tablename_', pytis.data.String()),
sql.Argument('key_value_', pytis.data.String()),
sql.Argument('detail_', pytis.data.String()),
sql.Argument('search_path_', pytis.data.String()))
result_type = VChanges
multirow = True
stability = 'stable'
depends_on = (
TChanges,
TChangesDetail,
VChanges,
)
