GHC 2020-03-11

1 comment.

, https://git.io/JvKdq in coleifer/peewee
How to use composite type as function argument (PostgresQL)
===========================================================

PostgresQL supports composite types (e.g. row types) as function arguments, but I can't figure out how to use a model as an argument of a `peewee.fn.func`.

Say we have this schema

```sql
CREATE TABLE IF NOT EXISTS post (
    id INTEGER NOT NULL PRIMARY KEY,
    votes INTEGER NOT NULL,
    posted TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```

and a function

```sql
CREATE OR REPLACE FUNCTION score(post)
	RETURNS double precision
	AS $$ SELECT $1.votes / EXTRACT(EPOCH FROM now() - $1.posted); $$
	LANGUAGE SQL
	STABLE;
```

we want to build this query:

```sql
SELECT id, score(post) FROM post;
```

How do we approach this?

I cannot use the model `Post` as an argument to `peewee.fn.score`:

```py
Post.select(Post.id, fn.score(Post))
# => peewee.ProgrammingError: can't adapt type 'ModelBase'
```

even `SQL` doesn't work unless I force an alias on the model:

```py
Post.select(Post.id, SQL("score(post)"))
# peewee.ProgrammingError: column "post" does not exist (because 'FROM "post" AS "t1"' in the constructed query)
```

```py
Post_ = Post.alias("post")
Post_.select(Post_.id, SQL("score(post)"))
# This one works.
```

I'm currently using the workaround above. Is there any better way to achieve this? Sorry if I missed something in the docs, I think I scoured every corner. (And sorry for the barrage of issues I've opened recently.)