[pphysch]

I think "SQL" is fine, whatever, I'm used to working with multiple different query and programming languages and dialects. That includes the freedom to define abstractions over SQL that meet my personal needs.

Standard SQL is not helpful, though. If that (failed) experiment was ended, database implementations would have even more freedom to explore superior syntax. Prescriptive language standards are a mistake.

[throwaway894345]

I like that SQL is a standard, and it's mostly "fine". Sure, I have to constantly read the man pages because there are half a dozen different ways to do fundamentally similar things, and there are subtle differences between each vendor, and I keep running into silly errors like trailing commas. But it mostly works.

The stuff that is more painful is building any kind of interesting application on top of a database. For example, as far as I know, it's very hard to "type check" a query (to get the "type" returned by a given query). It's also hard to efficiently compose SQL. And as far as I know, there's no standard, bulletproof way to escape SQL ("named parameters" is fine when you need to escape parameters, but most of SQL isn't parameters). There's also no good way to express sum types (a "place" can be a "park" or a "restaurant" or a "library", and each of those have different associated data--I don't need a "has_cycling_trails" boolean column for a restaurant, but I do for a park). There are various workarounds, all deeply unsatisfying.

[grebc]

In MSSQL you can select top 0 * into a temp table and retrieve all the usual column meta data.

I’ve written basic custom report writer functionality using this technique that lets users(usually me the developer or a super user) do custom sanitised SQL selects.

I assume similar functionality exists in all the different vendors databases.

[throwaway894345]

Yes, you can obviously run queries to get that information, but you can’t do it statically very easily.

> I’ve written basic custom report writer functionality using this technique that lets users(usually me the developer or a super user) do custom sanitised SQL selects.

I’m not sure how having the column metadata helps you sanitize SQL.

[grebc]

Not sure what you mean by done statically in the context of a report writer?

Columns are added, removed, pivoted, summed etc by the user running the report. This can’t be static but the OP was mentioning how you can’t get column meta data easily.

By sanitised SQL I mean the query is fed to the MSSQL parser and only select & union all is allowed as far queries go(eg. no delete, drops, updates etc).

[throwaway894345]

> Not sure what you mean by done statically in the context of a report writer?

Statically refers to taking some metadata about the database as well as the query and being able to anticipate the shape of the output columns without needing to run a query against a Postgres database. I'm not sure what you mean about "in the context of a report writer"--I don't think that's the use case I was describing in my original comment.

> Columns are added, removed, pivoted, summed etc by the user running the report. This can’t be static but the OP was mentioning how you can’t get column meta data easily.

I think I am the OP, right? And I think the report-writer stuff is confusing this conversation.

> By sanitised SQL I mean the query is fed to the MSSQL parser and only select & union all is allowed as far queries go(eg. no delete, drops, updates etc).

Enforcing read-only is helpful, but it's insufficient. Firstly, reads aren't always safe (e.g., reading confidential data) and secondly we also want the ability to safely generate queries that write or delete data.