Replies: 2 comments 2 replies
-
We used to do that for VIEWs and recently switched to not doing that, because after creation the object could get altered, quickly invalidating the stored sql. Instead we regenerate it |
Beta Was this translation helpful? Give feedback.
-
Thanks for looking into this, I see the reason for not storing the original code and its internal representation. In Firebird for ex., which stores both, this rarely resulted in "invalid BLR (binary language representation)", when the original and the internal code got out-of-sync for some reason. It should never happen, since one cannot edit the internal representation directly. Anyway, here's a small example of a macro/function body: original code: date_add(d::date, interval (m::integer) minute) regenerated code: date_add(CAST(d AS DATE), to_minutes(CAST(trunc(CAST(CAST(m AS INTEGER) AS DOUBLE)) AS BIGINT))) But if macros get more complex, this adds up. I wonder how to maintain business logic within duckdb. Maybe that's not a supported approach here, I'm quite new to duckdb and coming from DBMS with (selectable) Stored Procedures to hold business logic. That is a very powerful concept, not so much that it has to be procedural, it can also be functional, but in that the database is also the code. Something like a whole ETL stretch that can be run purely in the database. |
Beta Was this translation helpful? Give feedback.
-
With more complex macros, it would be helpful if the original SQL code was kept in the database. Right now, the original code gets preprocessed and the result of this processing gets stored as macro_definition, accessible through duckdb_functions().
During that process all formatting is lost and, more important, the stored SQL code is way more elaborate re type casts and other things, it certainly does the same job as the original code, but is definitely not what was submitted when defining the macro, much harder to read.
This makes replacing (updating) more complex macros difficult. I would expect that when I define database objects, I can retrieve them again from the database, more or less like I defined them, and not from somewhere else, if I happened to store them somewhere else. Is the underlying concept here to keep the original source code of macro definitions outside the database and treat a macro definition as a one-off process? Macros are the closest thing to stored procedures (the concept of having business logic within the database) duckdb has to offer, but with preprocessed stored macros, one has to maintain database objects in the application layer. Meaning two code sources and all the negative implications that incurs.
Beta Was this translation helpful? Give feedback.
All reactions