razeri_plugin_contract/routine_render/
table.rs

1// Copyright (C) 2026 Gabriel Shea
2// Licensed under either of the Apache License, Version 2.0 or the MIT
3// license, at your option.
4
5//! The table-valued-function grammars.
6
7use super::{
8    inline_parameters, note_above, or_replace, plain_name, quoted_name, quoted_text,
9    refusal_comment,
10};
11use crate::routine_form::TableFunctionForm;
12use crate::routine_spec::{RoutineBody, RoutineReturns, RoutineSpec};
13
14/// **Spell this table-valued function in a named grammar.**
15///
16/// Parameters:
17/// - `form`: the grammar to spell it in. `Custom` answers with the
18///   `PostgreSQL` form under a comment naming the grammar;
19/// - `routine`: the function, in the shape a `Custom` dialect receives it.
20#[must_use]
21pub fn render_table_function_as(form: TableFunctionForm, routine: &RoutineSpec) -> String {
22    // No wildcard arm, deliberately: a form added to the enum has to be
23    // spelled here or the build stops.
24    match form {
25        TableFunctionForm::PostgreSql => postgres(routine),
26        TableFunctionForm::TSql => t_sql(routine),
27        TableFunctionForm::Snowflake => snowflake(routine),
28        TableFunctionForm::Databricks => databricks(routine),
29        TableFunctionForm::GoogleSql => google_sql(routine),
30        TableFunctionForm::DuckDb => duckdb(routine),
31        TableFunctionForm::ClickHouse => clickhouse(routine),
32        TableFunctionForm::Custom(grammar) => note_above(
33            &format!(
34                "You asked for {grammar}, which is your dialect's own grammar and not one spelled \
35                 here. The PostgreSQL form below is the nearest neutral starting point; the named \
36                 forms are reachable by naming one."
37            ),
38            &postgres(routine),
39        ),
40        TableFunctionForm::Unsupported(orientation) => {
41            refusal_comment("table function", &routine.name, orientation)
42        }
43    }
44}
45
46/// The row shape, spelled the way most grammars spell it.
47fn row_shape(routine: &RoutineSpec) -> String {
48    match &routine.returns {
49        RoutineReturns::Columns(columns) => format!(
50            "TABLE({})",
51            columns
52                .iter()
53                .map(|c| format!("{} {}", c.name, c.data_type))
54                .collect::<Vec<_>>()
55                .join(", ")
56        ),
57        RoutineReturns::RowsOf(relation) => format!("SETOF {relation}"),
58        RoutineReturns::Value(data_type) => format!("SETOF {data_type}"),
59        RoutineReturns::Inferred | RoutineReturns::Nothing => "TABLE".to_owned(),
60    }
61}
62
63/// The query whose rows the function returns.
64fn body(routine: &RoutineSpec) -> String {
65    match &routine.body {
66        RoutineBody::Query(query) | RoutineBody::Block(query) => query.clone(),
67        RoutineBody::Expression(expression) => expression.clone(),
68        RoutineBody::Statements(statements) => statements.join(";\n"),
69    }
70}
71
72/// `PostgreSQL` and Citus: `RETURNS TABLE(…)` or `RETURNS SETOF …` over a
73/// dollar-quoted body.
74fn postgres(routine: &RoutineSpec) -> String {
75    format!(
76        "CREATE {}FUNCTION {}({})\nRETURNS {}\nLANGUAGE SQL\nAS $$\n{}\n$$;",
77        or_replace(routine),
78        plain_name(routine),
79        inline_parameters(routine),
80        row_shape(routine),
81        body(routine)
82    )
83}
84
85/// T-SQL's inline table-valued function. The row shape is not declared: the
86/// engine reads it off the query, and `RETURNS TABLE` is the whole clause.
87fn t_sql(routine: &RoutineSpec) -> String {
88    let create = if routine.or_replace { "CREATE OR ALTER FUNCTION" } else { "CREATE FUNCTION" };
89    let mut lines = vec![format!("{create} {}(", quoted_name(routine, '[', ']'))];
90    let last = routine.parameters.len().saturating_sub(1);
91    for (index, parameter) in routine.parameters.iter().enumerate() {
92        let comma = if index == last { "" } else { "," };
93        lines.push(format!("    @{} {}{}", parameter.name, parameter.data_type, comma));
94    }
95    lines.push(")".to_owned());
96    lines.push("RETURNS TABLE".to_owned());
97    lines.push("AS".to_owned());
98    lines.push("RETURN (".to_owned());
99    lines.push(format!("    {}", body(routine)));
100    lines.push(")".to_owned());
101    lines.join("\n")
102}
103
104/// Snowflake's UDTF, with the description as a clause.
105fn snowflake(routine: &RoutineSpec) -> String {
106    let mut lines = vec![
107        format!(
108            "CREATE {}FUNCTION {}({})",
109            or_replace(routine),
110            plain_name(routine),
111            inline_parameters(routine)
112        ),
113        format!("RETURNS {}", row_shape(routine)),
114        "LANGUAGE SQL".to_owned(),
115    ];
116    if let Some(comment) = &routine.comment {
117        lines.push(format!("COMMENT = '{}'", quoted_text(comment)));
118    }
119    lines.push("AS $$".to_owned());
120    lines.push(body(routine));
121    lines.push("$$;".to_owned());
122    lines.join("\n")
123}
124
125/// Databricks SQL: `RETURNS TABLE … RETURN <query>`, and no `LANGUAGE`.
126fn databricks(routine: &RoutineSpec) -> String {
127    let mut lines = vec![
128        format!(
129            "CREATE {}FUNCTION {}({})",
130            or_replace(routine),
131            plain_name(routine),
132            inline_parameters(routine)
133        ),
134        format!("RETURNS {}", row_shape(routine)),
135    ];
136    if let Some(comment) = &routine.comment {
137        lines.push(format!("COMMENT '{}'", quoted_text(comment)));
138    }
139    lines.push(format!("RETURN {}", body(routine)));
140    lines.join("\n")
141}
142
143/// `GoogleSQL`'s `CREATE TABLE FUNCTION`. The row shape is read off the query.
144fn google_sql(routine: &RoutineSpec) -> String {
145    format!(
146        "CREATE {}TABLE FUNCTION {}({}) AS (\n{}\n);",
147        or_replace(routine),
148        quoted_name(routine, '`', '`'),
149        inline_parameters(routine),
150        body(routine)
151    )
152}
153
154/// `DuckDB`'s table `MACRO`. There is no `RETURNS` clause at all, which is why
155/// riding the `PostgreSQL` form here is a parse error at `RETURNS`.
156fn duckdb(routine: &RoutineSpec) -> String {
157    let parameters =
158        routine.parameters.iter().map(|p| p.name.clone()).collect::<Vec<_>>().join(", ");
159    format!(
160        "CREATE {}MACRO {}({}) AS TABLE\n{}",
161        or_replace(routine),
162        plain_name(routine),
163        parameters,
164        body(routine)
165    )
166}
167
168/// `ClickHouse`'s parameterized view. It is a view and not a function, which is
169/// why the parameters are not in the statement at all: the query addresses them
170/// as `{name:Type}` and the caller supplies them at the call site.
171fn clickhouse(routine: &RoutineSpec) -> String {
172    format!("CREATE {}VIEW {} AS\n{}", or_replace(routine), plain_name(routine), body(routine))
173}
174
175#[cfg(test)]
176mod tests {
177    use super::*;
178    use crate::routine_spec::{ParameterDirection, RoutineColumn, RoutineKind, RoutineParameter};
179
180    fn a_function() -> RoutineSpec {
181        let mut spec = RoutineSpec::new(RoutineKind::TableFunction, "customer_at");
182        spec.schema = Some("retail_core".to_owned());
183        spec.or_replace = true;
184        spec.comment = Some("The customers as they stood.".to_owned());
185        spec.parameters = vec![RoutineParameter {
186            name: "p_as_of".to_owned(),
187            data_type: "DATE".to_owned(),
188            declared_type: None,
189            direction: ParameterDirection::In,
190            default: None,
191        }];
192        spec.returns = RoutineReturns::Columns(vec![
193            RoutineColumn {
194                name: "customer_sid".to_owned(),
195                data_type: "BIGINT".to_owned(),
196                declared_type: None,
197            },
198            RoutineColumn {
199                name: "customer_name".to_owned(),
200                data_type: "VARCHAR(200)".to_owned(),
201                declared_type: None,
202            },
203        ]);
204        spec.body =
205            RoutineBody::Query("SELECT customer_sid, customer_name FROM customer".to_owned());
206        spec
207    }
208
209    /// `PostgreSQL`, whole.
210    #[test]
211    fn the_postgres_form_declares_the_row_shape() {
212        assert_eq!(
213            render_table_function_as(TableFunctionForm::PostgreSql, &a_function()),
214            "CREATE OR REPLACE FUNCTION retail_core.customer_at(p_as_of DATE)\nRETURNS \
215             TABLE(customer_sid BIGINT, customer_name VARCHAR(200))\nLANGUAGE SQL\nAS \
216             $$\nSELECT customer_sid, customer_name FROM customer\n$$;"
217        );
218    }
219
220    /// **`RETURNS SETOF` is one column, and a routine declaring several has
221    /// nowhere to put them.** A spec asking for rows of an existing relation gets
222    /// the `SETOF` spelling and nothing else.
223    #[test]
224    fn rows_of_a_relation_are_setof_and_not_a_column_list() {
225        let mut spec = a_function();
226        spec.returns = RoutineReturns::RowsOf("customer".to_owned());
227        assert!(
228            render_table_function_as(TableFunctionForm::PostgreSql, &spec)
229                .contains("RETURNS SETOF customer\n")
230        );
231    }
232
233    /// **T-SQL reads the row shape off the query.** `RETURNS TABLE` is the whole
234    /// clause for an inline TVF, and a column list there is a syntax error.
235    #[test]
236    fn the_t_sql_form_declares_no_columns() {
237        let rendered = render_table_function_as(TableFunctionForm::TSql, &a_function());
238        assert_eq!(
239            rendered,
240            "CREATE OR ALTER FUNCTION [retail_core].[customer_at](\n    @p_as_of \
241             DATE\n)\nRETURNS TABLE\nAS\nRETURN (\n    SELECT customer_sid, customer_name FROM \
242             customer\n)"
243        );
244        assert!(!rendered.contains("customer_sid BIGINT"), "{rendered}");
245    }
246
247    /// **`DuckDB` has no `RETURNS` clause at all**, which is why riding the
248    /// `PostgreSQL` form here is a parse error at `RETURNS`.
249    #[test]
250    fn the_duckdb_macro_has_no_returns_clause() {
251        let rendered = render_table_function_as(TableFunctionForm::DuckDb, &a_function());
252        assert_eq!(
253            rendered,
254            "CREATE OR REPLACE MACRO retail_core.customer_at(p_as_of) AS TABLE\nSELECT \
255             customer_sid, customer_name FROM customer"
256        );
257        assert!(!rendered.contains("RETURNS"), "{rendered}");
258    }
259
260    /// Snowflake, Databricks and `GoogleSQL`, whole.
261    #[test]
262    fn the_remaining_forms_carry_their_own_clauses() {
263        assert!(
264            render_table_function_as(TableFunctionForm::Snowflake, &a_function())
265                .contains("\nLANGUAGE SQL\nCOMMENT = 'The customers as they stood.'\nAS $$\n")
266        );
267        assert!(
268            render_table_function_as(TableFunctionForm::Databricks, &a_function())
269                .ends_with("RETURN SELECT customer_sid, customer_name FROM customer")
270        );
271        assert!(
272            render_table_function_as(TableFunctionForm::GoogleSql, &a_function())
273                .starts_with("CREATE OR REPLACE TABLE FUNCTION `retail_core`.`customer_at`(")
274        );
275    }
276
277    /// **`ClickHouse` has a parameterized view and not a function**, so the
278    /// parameters are not in the statement: the query addresses them and the
279    /// caller supplies them at the call site.
280    #[test]
281    fn the_clickhouse_form_is_a_view_and_carries_no_parameter_list() {
282        let rendered = render_table_function_as(TableFunctionForm::ClickHouse, &a_function());
283        assert_eq!(
284            rendered,
285            "CREATE OR REPLACE VIEW retail_core.customer_at AS\nSELECT customer_sid, \
286             customer_name FROM customer"
287        );
288        assert!(!rendered.contains("p_as_of"), "{rendered}");
289    }
290
291    /// A refusal is the dialect's own sentence in a comment, and never a
292    /// statement.
293    #[test]
294    fn an_unsupported_form_spells_no_statement() {
295        let rendered = render_table_function_as(
296            TableFunctionForm::Unsupported(
297                "a VIEW returns rows here, with the parameter in the \
298                                           caller's own WHERE clause",
299            ),
300            &a_function(),
301        );
302        assert!(rendered.contains("a VIEW returns rows here"));
303        assert!(!rendered.contains("CREATE"), "{rendered}");
304    }
305}
razeri_plugin_contract/routine_render/table.rs

razeri_plugin_contract/routine_render/
table.rs
