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, )