Extend computed columns docs with more examples, also rename to "computed fields"

[wolfgangwalther]

From time to time we get feature request where we suggest to use computed columns instead. Maybe we can extend the docs here a bit with more examples what can be done with those columns. We should also move the section out of the "vertical filtering" subsection, one level up. It's hard to find in there and it does not only apply to vertical filtering, but also to horizontal filtering and ordering.

Collecting issues where we suggest computed columns here, for reference:

• Feature Request: Ability to use cast syntax on order params postgrest#1651
• Using of custom functions in WHERE clause postgrest#1613

[wolfgangwalther]

Just noticed #355. I missed that.

[wolfgangwalther]

Also noted this comment:

Computed columns must be created under the exposed schema to be used in this way.

This seems to be wrong - I use computed columns sucessfully in the extra-search-path schemas. This has the advantage, that they don't show up as RPCs in the OpenAPI output.

[steve-chavez]

I agree. Computed columns should have his own page. It shouldn't be part of API.

We could also mention generated columns there.

[wolfgangwalther]

I agree. Computed columns should have his own page. It shouldn't be part of API.

Hm. Where do you suggest they should go?

When I was just looking for them, before creating this issue, I was intuitively looking in the API section (just not nested in the vertical filtering). I was expecting them somewhere there.

[steve-chavez]

I was thinking that computed columns were more about Custom Queries(or maybe "extending postgrest") and that the API page was getting long. So I thought it could be its own reference page.

(Another example, I think HTTP logic can have a dedicated page)

[wolfgangwalther]

Ah, I see where you're coming from. Looking at it this way, I feel like API is indeed a bit long.

What do you think about a split across the HTTP vs SQL line? So:

1. One big chapter (currently API) that is all about the request syntax. Basic question: "How is a request mapped to an SQL statement?". Example code would be almost exclusively curl examples and responses.

2. Another big chapter (currently spread out across various sections) that is all about the SQL. Basic question: "How to design my database for best use with PostgREST?". Example code would be mostly SQL code. The following sections could be part of that:

• Everything in schema structure
• Computed columns
• HTTP logic
• some parts of Stored Procedures (split that up into "How to request?" and "How to write?")

The "Custom Queries" page is more or less a transition between the two parts along the lines of: If you hit the limits with just the request syntax, you might have to do in-database stuff.

This would also nicely separate the use-cases:

• set up a postgrest instance in front of an existing database (mostly request syntax) and
• develop a new database from scratch for use with postgrest (mostly sql)

I feel like "Configuration" could also go into "Administration". That should give us a nice structure of 3 main topics.

[steve-chavez]

I feel like "Configuration" could also go into "Administration"

You know, it used to be that way. But I found myself always searching and pointing users to the config section inside "Administration". So I made Configuration its own reference page. This also goes in line with divio's reference concept.

"Administration" is more loaded with sysadmin tasks(nginx, systemd), so the config got a bit lost there.

One big chapter (currently API) that is all about the request syntax. Basic question: "How is a request mapped to an SQL statement?". Example code would be almost exclusively curl examples and responses.

That sounds good, I feel like that would be an explanation.

Another big chapter (currently spread out across various sections) that is all about the SQL. Basic question: "How to design my database for best use with PostgREST?". Example code would be mostly SQL code. The following sections could be part of that:
Everything in schema structure
Computed columns
HTTP logic
some parts of Stored Procedures (split that up into "How to request?" and "How to write?")

Yes, sounds that would be an extended Schema Structure. A word of care though, with your suggestion we'd be mixing references(http logic, sps, etc) and explanations(schema structure). I think it's best to follow the divio guides on this matter. We should keep them separate, otherwise docs will become hard to manage.

[wolfgangwalther]

I feel like "Configuration" could also go into "Administration"

You know, it used to be that way. But I found myself always searching and pointing users to the config section inside "Administration". So I made Configuration its own reference page. This also goes in line with divio's reference concept.

"Administration" is more loaded with sysadmin tasks(nginx, systemd), so the config got a bit lost there.

Got it, makes sense. Configuration certainly sticks out more like this.

One big chapter (currently API) that is all about the request syntax. Basic question: "How is a request mapped to an SQL statement?". Example code would be almost exclusively curl examples and responses.

That sounds good, I feel like that would be an explanation.

Maybe the "basic question" was hinting in the wrong way, but I certainly meant this in a reference way, not explanation.

Another big chapter (currently spread out across various sections) that is all about the SQL. Basic question: "How to design my database for best use with PostgREST?". Example code would be mostly SQL code. The following sections could be part of that:
Everything in schema structure
Computed columns
HTTP logic
some parts of Stored Procedures (split that up into "How to request?" and "How to write?")

Yes, sounds that would be an extended Schema Structure. A word of care though, with your suggestion we'd be mixing references(http logic, sps, etc) and explanations(schema structure). I think it's best to follow the divio guides on this matter. We should keep them separate, otherwise docs will become hard to manage.

I absolutely I agree. In terms of divio, I suggest to do the following:

• 1 chapter of API (=query syntax / "HTTP API") reference
• 1 chapter of API (=sql stuff / "SQL API") reference

You mentioned something like "Behind the scenes" somewhere else and we already have "Howto", so those two could be more on the explanation side - one of them rather practical and of them more theoretical.

Maybe I did not look carefully enough into "Schema structure" and not all of that is really reference. I certainly think that only the reference stuff should go into "SQL API", the explanation stuff maybe in "Behind the scenes" or "Howto" - wherever it fits more.

[steve-chavez]

With the pg generated columns, that says:

PostgreSQL currently implements only stored generated columns.

The term "computed columns" is now a bit confusing, users think computed columns are not possible.

So I think we should rename to "computed fields", the term that PostgreSQL uses here.

This equivalence between functional notation and field notation makes it possible to use functions on composite types to implement “computed fields”.
An application using the last query above wouldn't need to be directly aware that somefunc isn't a real column of the table.