Open xxchan opened 1 year ago
Some offline discussions:
setof
UDTF, we also directly inline it to subquery. This behaviour is different from PG, and I prefer not implementing it to doing it differently with PG. So at least we need further discussion for this.Btw, I think we can probably encourage our users to use DBT's macro in the case where they need UDF. My major concern with this feature is the engineering complexity that it'll introduce to our system.
Not very familiar with DBT. I'm wondering is it easy to adopt for users unfamiliar with it? Will it require users to change their workflow? Or can be used as a handy Swiss Army Knife? 🤔
Complexity is definitely the concern, and also Eric's reason for opposing. But maybe the simplist expression
case is most useful and has very low complexity.
e.g.,
Yes -- say I need to parse jsonb to handle missing values and casting, I don't necessarily want extra piece of infrastructure (python server) when a sql expression could be enough
For such use case, maybe users don't want extra DBT neither.
This issue has been open for 60 days with no activity.
If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity
label.
You can also confidently close this issue as not planned to keep our backlog clean. Don't worry if you think the issue is still valuable to continue in the future. It's searchable and can be reopened when it's time. 😄
is it done? Can we close the issue?
Motivation
Reusability
e.g.,
select * from orders where id = $1 and deleted = false and create_date > $2
Easier to migrate from other SQL dialects to RisingWave
Different SQL dialects (Flink/Hive/Clickhouse) have different syntax sugars. Usually there are equivalent functions, but rewriting SQL can be error-prone. If we allow them to define a SQL UDF as an alias, they don't need to modify their SQLs at all.
Users are very familiar with their original SQL, and the less changes made to the SQL logic, the lower the migration cost will be.
This is one example https://github.com/risingwavelabs/risingwave/pull/10145
Feature subset we can support
LANGUAGE SQL
is also quite complex, as it allows multiple statements (DMLs are allowed). We won't want to support this now (and don't support multi-statement).But a subset of SQL UDF won't be hard to implement. Specifically, we can support those can be inlined as a subquery. And we can implement the features one by one. Even we only support the simplest forms, it's still useful.
sql_body
is likeA note from PG doc about this form:
definition
is a SQL string1 returns a value (1 row 1 col)
1.1
RETURN
expressionThis should be trivial.
1.2
SELECT
an expression1.3
SELECT
multiple expressionse.g.,
This needs to change multiple cols to a struct. Cannot simply convert
select f(), ...
toselect (select 1,2), ...
(more than 1 cols).1.4 polymorphic arguments
e.g.,
1.5
SELECT
withFROM
e.g., agg and point-get query. might consider together with 2.
2. returns a table (
setof
) (n rows 1 col)This includes: table functions and more general
SELECT
subquery.Postgres executes the SQL UDF in
ProjectSet
. It might be possible to convert it to a JOIN. Consider it later.e.g.,
Cannot simply convert to
select (select * from t), ...
(more than 1 rows).Some points might need to take care of
OUT
parameter vsRETURNS
type)Reference
CREATE FUNCTION
)LANGUAGE SQL
)