Table-Returning Functions

[scott-w]

It would be nice to be able to treat a function that returns a table as a Model in Django.

I'm thinking something along the lines of what's already in the View class, but a function gets called instead.

The reason for using a function is to create more powerful "views" that can be generated, and queried, at runtime.

It will need extra information so that the sync command can create the function names, arguments etc.

An example:

class MyFunc(TableFunction):

    class Meta:
        arguments = (
           ( 'arg1',  'DATE'),
        )

        sql = 'SELECT my_column FROM my_table WHERE some_date = $1'

    my_column = models.CharField()

The idea is still a little poorly-formed in my mind, so I'll try and put some sample code together and see if I can get the result I want before pushing anything.

[scott-w]

Updated arguments to tuple as order matters.

[zacharyvoase]

I appreciate the use case, but I prefer to do this:

class MyView(View):
    projection = ['MyTable.my_column', 'MyTable.some_date']
    sql = 'SELECT my_column, some_date FROM my_table'

results = MyView.objects.filter(some_date=DATE)

You can typically just use a filter or JOIN predicate to accomplish the same thing as parametrisation, and with the right indexes Postgres will execute it in the best possible time.

[scott-w]

That works for most cases, I agree.

I was simplifying the model, but what I'm interested in is getting an aggregate (e.g. Sum) based on a date range, then using that aggregate to further filter the model.

As an example:

class MyFunc(TableFunction):

    class Meta:
        arguments = (
           ( 'start_date',  'DATE'),
           ('end_date', 'DATE'),
        )

        total_amount = IntegerField()

        sql = 'SELECT SUM(my_column) as total_amount FROM my_table WHERE start_date >= $1 AND end_date <= $2'

initial_queryset = MyFun.objects.call(date_from, date_to)

new_queryset = initial_queryset.filter(total_amount__gt=0)

If I can get a similar result from a View, then I agree that I'd rather do that. An issue I can see with this is changing functions whose call signature has changed - since the database might be using other functions that Django isn't aware of.

[zacharyvoase]

I wanted to revisit this in the context of prepared statements. Could you check those docs and see if they would be helpful?

If they aren't, I'd venture to say that this sort of thing is not even a Postgres-specific extension, but something you could do on all databases—the challenge lies simply in implementing it in the Django ORM, rather than on top of Postgres specifically. Would you agree with that statement?

[scott-w]

That could work. I'll look more into prepared statements to see if they have the effect I'm looking for. I should be able to start work on this over the next few months.

Cheers, Scott

[scott-w]

I've done some work with this and it looks like functions will be easier to implement for what I'm looking for.

You're right, it would be nicer to have this stuff in the orm, but it's easier to put it here in the meantime.

Anyway, I'll change the code to use functions, so we can prove the concept and see where things go from there.