Pgsql how to return a value from script
I need to return a value named tsmax from the execution of a pgsql script.
The script is executed by and ETL datasource (Pentaho). All I can do is execute a script. I can't create functions on source database.
Below is my script. I need something like select tsmax; at the end, but the script block cannot do select nor return values. (I've just replaced all calculations with a label "do some process to calculate tsmax" to simplify the example).
do $$
declare tsmax timestamp;
begin
-- do some process to calculate tsmax
-- do some process to calculate tsmax
tsmax = now();
-- i want to return value tsmax
select tsmax; -- this is an ERROR!
end $$ language plpgsql;
Thanks
postgresql plpgsql
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
add a comment |
I need to return a value named tsmax from the execution of a pgsql script.
The script is executed by and ETL datasource (Pentaho). All I can do is execute a script. I can't create functions on source database.
Below is my script. I need something like select tsmax; at the end, but the script block cannot do select nor return values. (I've just replaced all calculations with a label "do some process to calculate tsmax" to simplify the example).
do $$
declare tsmax timestamp;
begin
-- do some process to calculate tsmax
-- do some process to calculate tsmax
tsmax = now();
-- i want to return value tsmax
select tsmax; -- this is an ERROR!
end $$ language plpgsql;
Thanks
postgresql plpgsql
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
You can useselect now()outside of the script - that value does not change inside of a single transaction
– a_horse_with_no_name
Nov 13 '18 at 11:34
"tsmax = now()" is an example! The real code to calculate tsmax is longer, with cursor and executing scripts.
– Radioleao
Nov 13 '18 at 11:40
add a comment |
I need to return a value named tsmax from the execution of a pgsql script.
The script is executed by and ETL datasource (Pentaho). All I can do is execute a script. I can't create functions on source database.
Below is my script. I need something like select tsmax; at the end, but the script block cannot do select nor return values. (I've just replaced all calculations with a label "do some process to calculate tsmax" to simplify the example).
do $$
declare tsmax timestamp;
begin
-- do some process to calculate tsmax
-- do some process to calculate tsmax
tsmax = now();
-- i want to return value tsmax
select tsmax; -- this is an ERROR!
end $$ language plpgsql;
Thanks
postgresql plpgsql
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
I need to return a value named tsmax from the execution of a pgsql script.
The script is executed by and ETL datasource (Pentaho). All I can do is execute a script. I can't create functions on source database.
Below is my script. I need something like select tsmax; at the end, but the script block cannot do select nor return values. (I've just replaced all calculations with a label "do some process to calculate tsmax" to simplify the example).
do $$
declare tsmax timestamp;
begin
-- do some process to calculate tsmax
-- do some process to calculate tsmax
tsmax = now();
-- i want to return value tsmax
select tsmax; -- this is an ERROR!
end $$ language plpgsql;
Thanks
postgresql plpgsql
postgresql plpgsql
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
edited Nov 13 '18 at 11:33
a_horse_with_no_name
293k46447541
293k46447541
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
asked Nov 13 '18 at 11:32
RadioleaoRadioleao
157213
157213
You can useselect now()outside of the script - that value does not change inside of a single transaction
– a_horse_with_no_name
Nov 13 '18 at 11:34
"tsmax = now()" is an example! The real code to calculate tsmax is longer, with cursor and executing scripts.
– Radioleao
Nov 13 '18 at 11:40
add a comment |
You can useselect now()outside of the script - that value does not change inside of a single transaction
– a_horse_with_no_name
Nov 13 '18 at 11:34
"tsmax = now()" is an example! The real code to calculate tsmax is longer, with cursor and executing scripts.
– Radioleao
Nov 13 '18 at 11:40
You can use
select now() outside of the script - that value does not change inside of a single transaction– a_horse_with_no_name
Nov 13 '18 at 11:34
You can use
select now() outside of the script - that value does not change inside of a single transaction– a_horse_with_no_name
Nov 13 '18 at 11:34
"tsmax = now()" is an example! The real code to calculate tsmax is longer, with cursor and executing scripts.
– Radioleao
Nov 13 '18 at 11:40
"tsmax = now()" is an example! The real code to calculate tsmax is longer, with cursor and executing scripts.
– Radioleao
Nov 13 '18 at 11:40
add a comment |
1 Answer
1
active
oldest
votes
You should to store value to session variable, and after execution of anonymous block, you can read this value:
[pavel@nemesis ~]$ echo "do $$ begin perform set_config('myvars.myvar', current_date::text, false); end $$; select current_setting('myvars.myvar'); " | psql -At postgres
DO
2018-11-13
DO command doesn't support any form of return value.
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
add a comment |
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%2f53280117%2fpgsql-how-to-return-a-value-from-script%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should to store value to session variable, and after execution of anonymous block, you can read this value:
[pavel@nemesis ~]$ echo "do $$ begin perform set_config('myvars.myvar', current_date::text, false); end $$; select current_setting('myvars.myvar'); " | psql -At postgres
DO
2018-11-13
DO command doesn't support any form of return value.
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
add a comment |
You should to store value to session variable, and after execution of anonymous block, you can read this value:
[pavel@nemesis ~]$ echo "do $$ begin perform set_config('myvars.myvar', current_date::text, false); end $$; select current_setting('myvars.myvar'); " | psql -At postgres
DO
2018-11-13
DO command doesn't support any form of return value.
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
add a comment |
You should to store value to session variable, and after execution of anonymous block, you can read this value:
[pavel@nemesis ~]$ echo "do $$ begin perform set_config('myvars.myvar', current_date::text, false); end $$; select current_setting('myvars.myvar'); " | psql -At postgres
DO
2018-11-13
DO command doesn't support any form of return value.
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
You should to store value to session variable, and after execution of anonymous block, you can read this value:
[pavel@nemesis ~]$ echo "do $$ begin perform set_config('myvars.myvar', current_date::text, false); end $$; select current_setting('myvars.myvar'); " | psql -At postgres
DO
2018-11-13
DO command doesn't support any form of return value.
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
answered Nov 13 '18 at 14:44
Pavel StehulePavel Stehule
22.5k34857
22.5k34857
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
add a comment |
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
I can set config var even if I have only select grants on a database?
– Radioleao
Nov 13 '18 at 15:36
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
@Radioleao yes, but you have to use some prefix like 'myvars.'
– Pavel Stehule
Nov 13 '18 at 16:45
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
It is good solution. Unfortunatly, it doesn't work with ETL Pentaho (table input task): it returns the error message "No result returned by the query". I don't know why, but I guess it's because the first statement (do block) doesn't return data.
– Radioleao
Nov 14 '18 at 9:16
add a comment |
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%2f53280117%2fpgsql-how-to-return-a-value-from-script%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
You can use
select now()outside of the script - that value does not change inside of a single transaction– a_horse_with_no_name
Nov 13 '18 at 11:34
"tsmax = now()" is an example! The real code to calculate tsmax is longer, with cursor and executing scripts.
– Radioleao
Nov 13 '18 at 11:40