postgresql procedure multiple select statement results as output
0
How do we get multiple result sets from Postgresql procedure. Below is the Procedure i created which is not working. I know that this is not how it works in Postgresql but unable to find the required answer anywhere. After this i need to get these multiple resultsets in Java JDBC.
CREATE OR REPLACE PROCEDURE public.validate_user_login(
a_username character varying,
a_password character varying,
a_ip character varying,
a_uuid character varying,
a_appid integer,
a_osname character varying,
a_osversion character varying,
a_devicemake character varying,
a_devicemodel character varying,
a_devicelat numeric,
a_devicelong numeric
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_tenant_id INTEGER;
v_user_id INTEGER;
v_tenant_device_id INTEGER;
statuscode INTEGER;
BEGIN
select user_id , tenant_id into v_tenant_id , v_user_id
from public.users
where username = a_username
and password = a_password;
if v_tenant_id > 0 AND v_user_id > 0
then
statuscode := 1;
select tenant_device_id into v_tenant_device_id from tenant_devices
where tenant_id = v_tenant_id
and uuid = a_uuid;
insert into login_history (user_id , app_id , geo_lat , geo_long ,ip_address , tenant_device_id)
VALUES (v_user_id, a_appid,a_devicelat ,a_devicelong , a_ip, v_tenant_device_id );
else
statuscode := -1;
end if;
select user_id , username , email , phone_no, alt_phone_no
from public.users
where username = a_username
and password = a_password
limit 1;
select role_id from
public.user_roles
where user_id = v_user_id;
select statuscode;
END
$BODY$;
postgresql procedure
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
add a comment |
0
How do we get multiple result sets from Postgresql procedure. Below is the Procedure i created which is not working. I know that this is not how it works in Postgresql but unable to find the required answer anywhere. After this i need to get these multiple resultsets in Java JDBC.
CREATE OR REPLACE PROCEDURE public.validate_user_login(
a_username character varying,
a_password character varying,
a_ip character varying,
a_uuid character varying,
a_appid integer,
a_osname character varying,
a_osversion character varying,
a_devicemake character varying,
a_devicemodel character varying,
a_devicelat numeric,
a_devicelong numeric
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_tenant_id INTEGER;
v_user_id INTEGER;
v_tenant_device_id INTEGER;
statuscode INTEGER;
BEGIN
select user_id , tenant_id into v_tenant_id , v_user_id
from public.users
where username = a_username
and password = a_password;
if v_tenant_id > 0 AND v_user_id > 0
then
statuscode := 1;
select tenant_device_id into v_tenant_device_id from tenant_devices
where tenant_id = v_tenant_id
and uuid = a_uuid;
insert into login_history (user_id , app_id , geo_lat , geo_long ,ip_address , tenant_device_id)
VALUES (v_user_id, a_appid,a_devicelat ,a_devicelong , a_ip, v_tenant_device_id );
else
statuscode := -1;
end if;
select user_id , username , email , phone_no, alt_phone_no
from public.users
where username = a_username
and password = a_password
limit 1;
select role_id from
public.user_roles
where user_id = v_user_id;
select statuscode;
END
$BODY$;
postgresql procedure
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
Assuming you indeed use Postgres 11, your procedure needs to return multiple refcursors as OUT parameters
– a_horse_with_no_name
Nov 22 '18 at 6:58
stackoverflow.com/q/50940438
– a_horse_with_no_name
Nov 22 '18 at 7:04
yes i am using postgresql 11
– kjpradeep
Nov 22 '18 at 10:10
add a comment |
0
0
0
How do we get multiple result sets from Postgresql procedure. Below is the Procedure i created which is not working. I know that this is not how it works in Postgresql but unable to find the required answer anywhere. After this i need to get these multiple resultsets in Java JDBC.
CREATE OR REPLACE PROCEDURE public.validate_user_login(
a_username character varying,
a_password character varying,
a_ip character varying,
a_uuid character varying,
a_appid integer,
a_osname character varying,
a_osversion character varying,
a_devicemake character varying,
a_devicemodel character varying,
a_devicelat numeric,
a_devicelong numeric
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_tenant_id INTEGER;
v_user_id INTEGER;
v_tenant_device_id INTEGER;
statuscode INTEGER;
BEGIN
select user_id , tenant_id into v_tenant_id , v_user_id
from public.users
where username = a_username
and password = a_password;
if v_tenant_id > 0 AND v_user_id > 0
then
statuscode := 1;
select tenant_device_id into v_tenant_device_id from tenant_devices
where tenant_id = v_tenant_id
and uuid = a_uuid;
insert into login_history (user_id , app_id , geo_lat , geo_long ,ip_address , tenant_device_id)
VALUES (v_user_id, a_appid,a_devicelat ,a_devicelong , a_ip, v_tenant_device_id );
else
statuscode := -1;
end if;
select user_id , username , email , phone_no, alt_phone_no
from public.users
where username = a_username
and password = a_password
limit 1;
select role_id from
public.user_roles
where user_id = v_user_id;
select statuscode;
END
$BODY$;
postgresql procedure
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
How do we get multiple result sets from Postgresql procedure. Below is the Procedure i created which is not working. I know that this is not how it works in Postgresql but unable to find the required answer anywhere. After this i need to get these multiple resultsets in Java JDBC.
CREATE OR REPLACE PROCEDURE public.validate_user_login(
a_username character varying,
a_password character varying,
a_ip character varying,
a_uuid character varying,
a_appid integer,
a_osname character varying,
a_osversion character varying,
a_devicemake character varying,
a_devicemodel character varying,
a_devicelat numeric,
a_devicelong numeric
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_tenant_id INTEGER;
v_user_id INTEGER;
v_tenant_device_id INTEGER;
statuscode INTEGER;
BEGIN
select user_id , tenant_id into v_tenant_id , v_user_id
from public.users
where username = a_username
and password = a_password;
if v_tenant_id > 0 AND v_user_id > 0
then
statuscode := 1;
select tenant_device_id into v_tenant_device_id from tenant_devices
where tenant_id = v_tenant_id
and uuid = a_uuid;
insert into login_history (user_id , app_id , geo_lat , geo_long ,ip_address , tenant_device_id)
VALUES (v_user_id, a_appid,a_devicelat ,a_devicelong , a_ip, v_tenant_device_id );
else
statuscode := -1;
end if;
select user_id , username , email , phone_no, alt_phone_no
from public.users
where username = a_username
and password = a_password
limit 1;
select role_id from
public.user_roles
where user_id = v_user_id;
select statuscode;
END
$BODY$;
postgresql procedure
postgresql procedure
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
asked Nov 22 '18 at 6:54
kjpradeepkjpradeep
13
13
Assuming you indeed use Postgres 11, your procedure needs to return multiple refcursors as OUT parameters
– a_horse_with_no_name
Nov 22 '18 at 6:58
stackoverflow.com/q/50940438
– a_horse_with_no_name
Nov 22 '18 at 7:04
yes i am using postgresql 11
– kjpradeep
Nov 22 '18 at 10:10
add a comment |
Assuming you indeed use Postgres 11, your procedure needs to return multiple refcursors as OUT parameters
– a_horse_with_no_name
Nov 22 '18 at 6:58
stackoverflow.com/q/50940438
– a_horse_with_no_name
Nov 22 '18 at 7:04
yes i am using postgresql 11
– kjpradeep
Nov 22 '18 at 10:10
Assuming you indeed use Postgres 11, your procedure needs to return multiple refcursors as OUT parameters
– a_horse_with_no_name
Nov 22 '18 at 6:58
Assuming you indeed use Postgres 11, your procedure needs to return multiple refcursors as OUT parameters
– a_horse_with_no_name
Nov 22 '18 at 6:58
stackoverflow.com/q/50940438
– a_horse_with_no_name
Nov 22 '18 at 7:04
stackoverflow.com/q/50940438
– a_horse_with_no_name
Nov 22 '18 at 7:04
yes i am using postgresql 11
– kjpradeep
Nov 22 '18 at 10:10
yes i am using postgresql 11
– kjpradeep
Nov 22 '18 at 10:10
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
});
}
});
draft saved
draft discarded
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%2f53425371%2fpostgresql-procedure-multiple-select-statement-results-as-output%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
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53425371%2fpostgresql-procedure-multiple-select-statement-results-as-output%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
Assuming you indeed use Postgres 11, your procedure needs to return multiple refcursors as OUT parameters
– a_horse_with_no_name
Nov 22 '18 at 6:58
stackoverflow.com/q/50940438
– a_horse_with_no_name
Nov 22 '18 at 7:04
yes i am using postgresql 11
– kjpradeep
Nov 22 '18 at 10:10