H2 SYSTEM_RANGE params from another table
So here's the situation. We essentially have a new business requirement that a certain process not write anything to disk. Up until now, it's been running on PostgreSQL, but since PostgreSQL doesn't support in-memory dbs, we're trying to switch to a DB that does. The best bet so far appears to be H2. (One reason for that is that some of our logic requires recursive queries, which H2 appears to support. It also mostly supports the same SQL style syntax, and I'd rather not rewrite hundreds of lines of SQL.) However, I'm running into some problems. We had some functions, and H2 doesn't support SQL functions (and I don't really want to recode our functions as Java, unless it's a lot easier than I'd thought), so I'm trying to inline them. The function was used as a field in a select statement, and returned multiple rows, using generate_series, basically performing an automatic JOIN. Note that the values passed to generate_series are derived from values in the row in question. (I.e., for each row of table T, 0-N rows may be present in the final output, depending on the row in T.)
However, things are slightly different in H2. Rather than generate_series, there is system_range, which A) is only indirectly documented (there's an extra step field mentioned nowhere in the documentation), and B) apparently must be used in the FROM clause, not as a SELECT expression. But if it's in the FROM clause as a JOIN, I don't have access to the fields of the other tables in the JOIN, and can't generate the needed range. (Note that we're dealing with timestamps, so the numbers are very likely too large to just generate the full possible range and select from it the range I want.)
So, how do I generate a dynamic N rows from a single row in H2? (Or, what's an alternate solution?)
postgresql join h2
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
add a comment |
So here's the situation. We essentially have a new business requirement that a certain process not write anything to disk. Up until now, it's been running on PostgreSQL, but since PostgreSQL doesn't support in-memory dbs, we're trying to switch to a DB that does. The best bet so far appears to be H2. (One reason for that is that some of our logic requires recursive queries, which H2 appears to support. It also mostly supports the same SQL style syntax, and I'd rather not rewrite hundreds of lines of SQL.) However, I'm running into some problems. We had some functions, and H2 doesn't support SQL functions (and I don't really want to recode our functions as Java, unless it's a lot easier than I'd thought), so I'm trying to inline them. The function was used as a field in a select statement, and returned multiple rows, using generate_series, basically performing an automatic JOIN. Note that the values passed to generate_series are derived from values in the row in question. (I.e., for each row of table T, 0-N rows may be present in the final output, depending on the row in T.)
However, things are slightly different in H2. Rather than generate_series, there is system_range, which A) is only indirectly documented (there's an extra step field mentioned nowhere in the documentation), and B) apparently must be used in the FROM clause, not as a SELECT expression. But if it's in the FROM clause as a JOIN, I don't have access to the fields of the other tables in the JOIN, and can't generate the needed range. (Note that we're dealing with timestamps, so the numbers are very likely too large to just generate the full possible range and select from it the range I want.)
So, how do I generate a dynamic N rows from a single row in H2? (Or, what's an alternate solution?)
postgresql join h2
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
add a comment |
So here's the situation. We essentially have a new business requirement that a certain process not write anything to disk. Up until now, it's been running on PostgreSQL, but since PostgreSQL doesn't support in-memory dbs, we're trying to switch to a DB that does. The best bet so far appears to be H2. (One reason for that is that some of our logic requires recursive queries, which H2 appears to support. It also mostly supports the same SQL style syntax, and I'd rather not rewrite hundreds of lines of SQL.) However, I'm running into some problems. We had some functions, and H2 doesn't support SQL functions (and I don't really want to recode our functions as Java, unless it's a lot easier than I'd thought), so I'm trying to inline them. The function was used as a field in a select statement, and returned multiple rows, using generate_series, basically performing an automatic JOIN. Note that the values passed to generate_series are derived from values in the row in question. (I.e., for each row of table T, 0-N rows may be present in the final output, depending on the row in T.)
However, things are slightly different in H2. Rather than generate_series, there is system_range, which A) is only indirectly documented (there's an extra step field mentioned nowhere in the documentation), and B) apparently must be used in the FROM clause, not as a SELECT expression. But if it's in the FROM clause as a JOIN, I don't have access to the fields of the other tables in the JOIN, and can't generate the needed range. (Note that we're dealing with timestamps, so the numbers are very likely too large to just generate the full possible range and select from it the range I want.)
So, how do I generate a dynamic N rows from a single row in H2? (Or, what's an alternate solution?)
postgresql join h2
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
So here's the situation. We essentially have a new business requirement that a certain process not write anything to disk. Up until now, it's been running on PostgreSQL, but since PostgreSQL doesn't support in-memory dbs, we're trying to switch to a DB that does. The best bet so far appears to be H2. (One reason for that is that some of our logic requires recursive queries, which H2 appears to support. It also mostly supports the same SQL style syntax, and I'd rather not rewrite hundreds of lines of SQL.) However, I'm running into some problems. We had some functions, and H2 doesn't support SQL functions (and I don't really want to recode our functions as Java, unless it's a lot easier than I'd thought), so I'm trying to inline them. The function was used as a field in a select statement, and returned multiple rows, using generate_series, basically performing an automatic JOIN. Note that the values passed to generate_series are derived from values in the row in question. (I.e., for each row of table T, 0-N rows may be present in the final output, depending on the row in T.)
However, things are slightly different in H2. Rather than generate_series, there is system_range, which A) is only indirectly documented (there's an extra step field mentioned nowhere in the documentation), and B) apparently must be used in the FROM clause, not as a SELECT expression. But if it's in the FROM clause as a JOIN, I don't have access to the fields of the other tables in the JOIN, and can't generate the needed range. (Note that we're dealing with timestamps, so the numbers are very likely too large to just generate the full possible range and select from it the range I want.)
So, how do I generate a dynamic N rows from a single row in H2? (Or, what's an alternate solution?)
postgresql join h2
postgresql join h2
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
asked Jan 1 at 23:15
ErhannisErhannis
2,55711429
2,55711429
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999678%2fh2-system-range-params-from-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53999678%2fh2-system-range-params-from-another-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
