# Implementing a table-valued function

**URL:** <https://forum.djangoproject.com/t/implementing-a-table-valued-function/45551>\
**Category:** ORM\
**Created:** [July 23, 2026, 1:37pm UTC](https://forum.djangoproject.com/t/implementing-a-table-valued-function/45551 "2026-07-23T13:37:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vtbassmatt](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/vtbassmatt/32/32894_2.png) [@vtbassmatt](https://forum.djangoproject.com/u/vtbassmatt)\
**Post date:** [July 23, 2026, 1:37pm UTC](https://forum.djangoproject.com/t/implementing-a-table-valued-function/45551/1 "2026-07-23T13:37:25Z")

</div>

tl;dr can anyone point me towards how I can ask the ORM to spit out just a table’s name, not `table.column`, in a SQL expression?

I’ve built some infrastructure around SQLite’s FTS5 system for a small, personal app. (I tried to link it, but as a new user, I’m only allowed 2 links which I’m spending elsewhere in this post. `sqlite.org/fts5.html` will get you there.) The design is roughly:

- A manual migration which creates the virtual table (code listing 1)
- An unmanaged table wrapping it (2)
- A method to perform a search (3)
- Infrastructure for keeping the FTS index up to date (outside the scope of what’s I’m asking for here)

My original search method used `.raw()` to invoke FTS5’s table-valued syntax: `SELECT * FROM search_v4(search_terms *);`. This gets me a RawQueryset, which is poorly-featured compared to a full Queryset (e.g. I can’t do things like `.first()` or take slices) and has some more quirks (one I recall is having to write `SELECT rowid, * ...` or else Paginator didn’t work, though I may be misremembering that).

My wrapper model should be capable of fully treating the virtual table as a (read-only) regular table, though, and that would get me a full Queryset. I went down a rabbit hole trying to get Django ORM to emit either table-valued function syntax or, as a fallback, FTS5’s other syntax: `SELECT * FROM table WHERE table = search_terms`. I want to be able to write `SearchDoc.objects.table_name('search terms')` or, in fallback terms, to something like `SearchDoc.objects.filter(search_v4='search terms')`.

The ORM really wants to express column references, not table references. I could not figure out how to write a standalone `Expression` or other Query API object that would give me the table name in the right place, nor how to plug it into the object manager as `.table_name()`. In my fallback syntax, `search_v4` isn’t a known field name, so that also fails.

I ended up with a hack: a Lookup which eats the field name in its `as_sql` method. This feels extremely brittle in ways I can’t quite put my finger on, and it’s unergonomic. To use it, you pick any arbitrary field knowing that it’ll be thrown away: `SearchDoc.objects.filter(body__table_match='search terms')`. It generates the `table = search_terms` version of the SQL. The code listing for this hack is shown below. I’d prefer to replace `.filter()` with some kind of `.table_name()` construct, or failing that, allow `search_v4=` (or more generically, perhaps `_table_name=`) as an argument to `filter`.

Can anyone point me in the right direction for where I’d look to inject this capability? To be clear, I’m not proposing Django adopt this as a core feature, I’m just looking to solve my problem with the least-hacky thing. It feels like between `Expression` and `Lookup` and all the other first-class ways to extend Django’s SQL generation, I must be missing something simple.

Code snippets:

```sql
-- 1: virtual table for FTS5
CREATE VIRTUAL TABLE search_v4 USING fts5(
    body,
    object_id UNINDEXED,
    content_type_id UNINDEXED,
    tokenize = 'trigram'
);

```

```python
// 2 - unmanaged wrapper table
class SearchDoc(models.Model):
    class Meta:
        managed = False
        db_table = 'search_v4'

    id = models.IntegerField(db_column='rowid', primary_key=True)
    # search document
    body = models.TextField()
    # search result rank
    rank = models.FloatField(null=True)
    # next 3 are links to searchable entities
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveBigIntegerField()
    content_object = GenericForeignKey("content_type", "object_id")

    def __str__ (self):
        return f'"{self.body.replace('\n', '\\n')}"'

```

```python
// 3 - current search implementation
def search(raw_terms: str):
    # see the SQLite fts5 docs about FTS5 strings
    quoted_terms = raw_terms.replace('"', '""')
    query_text = f'"{quoted_terms}" *'

    return (
        SearchDoc.objects
        .filter(body__table_match=query_text)
        .order_by('rank')
    )

```

```python
// 4 - <field>__table_match= hack
@models.Field.register_lookup
class TableMatch(models.Lookup):
    lookup_name = 'table_match'

    def as_sql(self, compiler, connection):
        # attempt to prevent this hack from being a source of unexplainable bugs
        if not isinstance(self.lhs, Col):
            raise ValueError('choose a column on the model to use __table_match')
        # use just the table alias as SQL
        lhs, lhs_params = self.lhs.alias, ()
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return "%s = %s" % (lhs, rhs), params

```

I searched existing forum posts, tickets, and DEPs before posting. I found a few that looked related, but nothing that solved my problem nor seemed like it was moving in that direction. For reference, they are about [adding generate\_series support](https://forum.djangoproject.com/t/gsoc-2026-add-support-for-generate-series-in-postgresql/44394) (a Postgres-specific table-valued function) and a related ticket (37115) + [DEP](https://github.com/django/deps/pull/113) - sorry for not linking the ticket, but again, I only get 2 links in this post ☹ . Although it’s using the words I want – table-valued function – the focus of that work seems to be on modeling CompositeField as a precursor. I think my use case is a much narrower thing which doesn’t require any new primitives like that, though I could be wrong.

Thank you!

---

<div class="post-metadata">

**Author:** ![Pravin](https://avatars.discourse-cdn.com/v4/letter/p/b38774/32.png) [@Pravin](https://forum.djangoproject.com/u/Pravin)\
**Post date:** [July 28, 2026, 3:32pm UTC](https://forum.djangoproject.com/t/implementing-a-table-valued-function/45551/2 "2026-07-28T15:32:05Z")

</div>

This is my first time working with FTS5, so I did a little research before replying. I found your question because I’m working on a related Django ORM feature in [PR #21636](https://github.com/django/django/pull/21636). That work involves multi-column subqueries.  
Please correct me , If I understand correctly, your migration creates the FTS5 virtual table, and the unmanaged model points to that table.

In `body__table_match`, the `body` field is only used to get the current table alias. The `body` column itself is not used.

I don’t think Django currently has a public API for referring only to a table, or for using a table-valued function directly in the `FROM` clause.

However, FTS5 provides a hidden column with the same name as the virtual table. You could map that column in your model:

```python
fts_query = models.TextField(
    db_column="search_v4",
    editable=False,
)

```

Then you could write:

```python
SearchDoc.objects.filter(fts_query=query_text)

```

Django should generate something similar to:

```sql
WHERE "search_v4"."search_v4" = %s

```

SQLite accepts this syntax and treats it as an FTS5 search.

You could hide this detail inside a custom queryset method:

```python
class SearchDocQuerySet(models.QuerySet):
    def search(self, query_text):
        return (
            self.filter(fts_query=query_text)
            .defer("fts_query")
            .order_by("rank")
        )

```

This returns a normal queryset, so methods such as `.filter()`, `.first()`, and slicing will still work.

---

<div class="post-metadata">

**Author:** ![webalpha107](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/webalpha107/32/32967_2.png) [@webalpha107](https://forum.djangoproject.com/u/webalpha107)\
**Post date:** [July 28, 2026, 11:34pm UTC](https://forum.djangoproject.com/t/implementing-a-table-valued-function/45551/3 "2026-07-28T23:34:26Z")

</div>

You’re not really fighting `Lookup` here—you’re fighting a deeper assumption in Django’s ORM:

> **The ORM is built around expressions that evaluate to values, not relations.**

A table name (or table alias) in `FROM`/`MATCH` position is not an expression in SQL. It’s part of the query structure. Django has lots of extension points for expressions (`Func`, `Expression`, `Lookup`, `Transform`, etc.), but very few for introducing new relation-level constructs.

A few thoughts on the options you’ve explored:

### Your `Lookup` isn’t as hacky as it looks

The implementation

```auto
lhs = self.lhs.alias
return "%s = %s" % (lhs, rhs), params

```

is actually fairly reasonable because `Col.alias` is the compiler’s notion of the table alias, which is exactly what SQLite FTS expects.

I’d probably use

```auto
compiler.quote_name_unless_alias(self.lhs.alias)

```

rather than emitting the alias directly, but conceptually it’s sound.

The ugly part is purely the API:

```auto
.filter(body__table_match=query)

```

where `body` is ignored.

* * *

### A custom `QuerySet` method is probably the nicest API

Instead of exposing the lookup, wrap it:

```auto
class SearchQuerySet(models.QuerySet):
    def match(self, query):
        return self.filter(body__table_match=query)

class SearchDoc(models.Model):
    objects = SearchQuerySet.as_manager()

```

Then callers write

```auto
SearchDoc.objects.match(query).order_by("rank")

```

The implementation still uses the lookup internally, but nobody else has to know.

* * *

### Why `filter(search_v4=query)` won’t work

The keyword arguments to `filter()` are parsed as

```auto
field__lookup

```

where `field` must resolve to a model field (or annotation).

There’s no extension hook that says “if this isn’t a field, treat it as the table.”

So something like

```auto
.filter(search_v4=query)

```

can’t really be implemented without modifying the ORM’s field resolution.

* * *

### Expressions won’t help

You mentioned trying `Expression`.

The problem is that expressions compile to things like

```auto
LOWER(body)
body
COALESCE(...)

```

They don’t compile to relation identifiers.

Even if you wrote

```auto
class TableName(Expression):
    ...

```

there’s nowhere legal to put that expression so that Django would emit

```auto
search_v4 = ?

```

instead of

```auto
(TableName(...)) = ?

```

The compiler simply isn’t expecting relation references there.

* * *

### A custom `Query` is theoretically possible…

…but it’s much more invasive.

You could subclass `Query` or `SQLCompiler` and override parts of `build_filter()` or `WhereNode` generation so that a sentinel expression produces the FTS syntax.

That’s a lot more fragile than your current lookup and depends on internal APIs that change fairly regularly.

I wouldn’t recommend it unless you’re building a reusable backend.

* * *

### If you specifically want the table-valued syntax

```auto
FROM search_v4(?)

```

that’s even harder.

That changes the `FROM` clause itself, which Django currently has essentially no public extension point for.

The work around table-valued functions and `generate_series()` that you found exists precisely because Django doesn’t currently have a good abstraction for relation-valued expressions.

* * *

## My recommendation

I would keep the lookup and hide it behind a queryset method:

```auto
class SearchQuerySet(models.QuerySet):
    def search(self, terms):
        quoted = terms.replace('"', '""')
        query = f'"{quoted}" *'
        return self.filter(body__table_match=query)

```

so callers simply write

```auto
SearchDoc.objects.search("foo").order_by("rank")

```

That gives you a clean public API while confining the “abuse” of `Lookup` to one implementation detail.

In fact, if this were proposed on Django’s internals list, I suspect maintainers would probably consider a custom lookup plus a queryset method to be the idiomatic solution today. Until Django grows first-class support for relation-valued expressions (the work around table-valued functions is moving in that direction), there isn’t a cleaner public extension point for “emit the table alias here instead of a column.”

---

<div class="post-metadata">

**Author:** ![vtbassmatt](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/vtbassmatt/32/32894_2.png) [@vtbassmatt](https://forum.djangoproject.com/u/vtbassmatt)\
**Post date:** [July 29, 2026, 1:13am UTC](https://forum.djangoproject.com/t/implementing-a-table-valued-function/45551/4 "2026-07-29T01:13:18Z")

</div>

Thank you both VERY much! I appreciate you taking the time to help me understand both SQLite FTS and Django internals better. I’m also relieved to know that my Lookup hack isn’t as gross as I feared.