postgres - function returning SET OF RECORD - errorreturning set of records in stored procedureError:...
How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?
How can I keep my gold safe from other PCs?
Is the UK legally prevented from having another referendum on Brexit?
Inversion applicable to three-dimensional lattices only
Including proofs of known theorems in master's thesis
Limits of Rolle theorem
Is the Maximum Use License for Everybody (MULE) a FOSS license?
In a post apocalypse world, with no power and few survivors, would Satnav still work?
Alternate timeline nomenclature
User input happy birthday program
How does a mid-19 century Military combat a modernized one?
If I tried and failed to start my own business, how do I apply for a job without job experience?
Finding the index of a specific element in a list
Trying to use an LCD 1602 to display, but I don't have a 10 KOhm potentiometer
What does an unprocessed RAW file look like?
How can guns be countered by melee combat without raw-ability or exceptional explanations?
How to wrap a figure in exam document?
How long can the stop in a stop-and-go be?
How can I handle players killing my NPC outside of combat?
Why don't you get burned by the wood benches in a sauna?
Is there any way to play D&D without a DM?
Performance and power usage for Raspberry Pi in the Stratosphere
Disk space full during insert, what happens?
Can you help me solve this algebra problem?
postgres - function returning SET OF RECORD - error
returning set of records in stored procedureError: set_valued function called in context that cannot accept a set. What is it about?RETURN NEXT in Postgres FunctionPostgreSQL 9.5, getting “cached plan must not change result type” errorHow do you declare a set-returning-function to only be allowed in the FROM clause?Postgres full text search with better matchIs it possible to create a user defined type with plpgsql or SQL?Understanding Set Returning Function (SRF) in the SELECT ListPostgres: query on huge (11gb ) index does not returnDynamically define a RETURN table (column type, name) for subsequent loop
I created a function:
CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$
DECLARE
cup_ids int[] := null;
sup_ids int[] := null;
cup record;
ous_ids_1 int[] := null;
ous_ids_2 int[] := null;
ous_ids int[] := null;
scopes setof record;
BEGIN
select into cup * from company_companyuserprofile where id = uid;
cup_ids := (select array(
select distinct employee_id from company_managerrelation cm
where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'
union
select distinct employee_id from company_dynamicrelation
where target_id = uid
));
sup_ids := (select array (
select distinct supervisory_id::int from company_companyuserprofile cup
where id = ANY(cup_ids)
));
IF not cup.supervisory_id is null and cup.supervisory_id <> '' then
ous_ids_1 := (select array (
select distinct id from company_companyou where
id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'
));
end if;
IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then
ous_ids_2 := (select array (
select distinct id from company_companyou where
id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'
));
end if;
ous_ids := ous_ids_1 || ous_ids_2;
scopes := (select a.id, a.title, b.type from
company_companyou a left outer join company_companyoutype b on a.type_id = b.id
where a.id = any(ous_ids) and b.type = 'Production Scope');
return scopes;
END;
$$ LANGUAGE plpgsql;
select get_managed_user_ids(26);
but I get this error:
[42601] ERROR: invalid type name "setof record"
I googled on the web but I haven't found an example to return a set of rows that fits my needs.
replacing return scopes;
with return next scopes;
or return query scopes;
is not valid.
Anyone can help me on this?
postgresql plpgsql set-returning-functions
add a comment |
I created a function:
CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$
DECLARE
cup_ids int[] := null;
sup_ids int[] := null;
cup record;
ous_ids_1 int[] := null;
ous_ids_2 int[] := null;
ous_ids int[] := null;
scopes setof record;
BEGIN
select into cup * from company_companyuserprofile where id = uid;
cup_ids := (select array(
select distinct employee_id from company_managerrelation cm
where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'
union
select distinct employee_id from company_dynamicrelation
where target_id = uid
));
sup_ids := (select array (
select distinct supervisory_id::int from company_companyuserprofile cup
where id = ANY(cup_ids)
));
IF not cup.supervisory_id is null and cup.supervisory_id <> '' then
ous_ids_1 := (select array (
select distinct id from company_companyou where
id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'
));
end if;
IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then
ous_ids_2 := (select array (
select distinct id from company_companyou where
id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'
));
end if;
ous_ids := ous_ids_1 || ous_ids_2;
scopes := (select a.id, a.title, b.type from
company_companyou a left outer join company_companyoutype b on a.type_id = b.id
where a.id = any(ous_ids) and b.type = 'Production Scope');
return scopes;
END;
$$ LANGUAGE plpgsql;
select get_managed_user_ids(26);
but I get this error:
[42601] ERROR: invalid type name "setof record"
I googled on the web but I haven't found an example to return a set of rows that fits my needs.
replacing return scopes;
with return next scopes;
or return query scopes;
is not valid.
Anyone can help me on this?
postgresql plpgsql set-returning-functions
add a comment |
I created a function:
CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$
DECLARE
cup_ids int[] := null;
sup_ids int[] := null;
cup record;
ous_ids_1 int[] := null;
ous_ids_2 int[] := null;
ous_ids int[] := null;
scopes setof record;
BEGIN
select into cup * from company_companyuserprofile where id = uid;
cup_ids := (select array(
select distinct employee_id from company_managerrelation cm
where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'
union
select distinct employee_id from company_dynamicrelation
where target_id = uid
));
sup_ids := (select array (
select distinct supervisory_id::int from company_companyuserprofile cup
where id = ANY(cup_ids)
));
IF not cup.supervisory_id is null and cup.supervisory_id <> '' then
ous_ids_1 := (select array (
select distinct id from company_companyou where
id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'
));
end if;
IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then
ous_ids_2 := (select array (
select distinct id from company_companyou where
id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'
));
end if;
ous_ids := ous_ids_1 || ous_ids_2;
scopes := (select a.id, a.title, b.type from
company_companyou a left outer join company_companyoutype b on a.type_id = b.id
where a.id = any(ous_ids) and b.type = 'Production Scope');
return scopes;
END;
$$ LANGUAGE plpgsql;
select get_managed_user_ids(26);
but I get this error:
[42601] ERROR: invalid type name "setof record"
I googled on the web but I haven't found an example to return a set of rows that fits my needs.
replacing return scopes;
with return next scopes;
or return query scopes;
is not valid.
Anyone can help me on this?
postgresql plpgsql set-returning-functions
I created a function:
CREATE OR REPLACE FUNCTION get_managed_user_ids(uid integer) RETURNS int[] AS $$
DECLARE
cup_ids int[] := null;
sup_ids int[] := null;
cup record;
ous_ids_1 int[] := null;
ous_ids_2 int[] := null;
ous_ids int[] := null;
scopes setof record;
BEGIN
select into cup * from company_companyuserprofile where id = uid;
cup_ids := (select array(
select distinct employee_id from company_managerrelation cm
where cm.manager_id = uid and cm.mgr_type = 'Direct Mgr'
union
select distinct employee_id from company_dynamicrelation
where target_id = uid
));
sup_ids := (select array (
select distinct supervisory_id::int from company_companyuserprofile cup
where id = ANY(cup_ids)
));
IF not cup.supervisory_id is null and cup.supervisory_id <> '' then
ous_ids_1 := (select array (
select distinct id from company_companyou where
id = cup.supervisory_id::int or id = ANY(sup_ids) or ous_chain_tree::text LIKE '%$' || cup.supervisory_id || '>%'
));
end if;
IF not cup.prd_scope_id is null and cup.prd_scope_id <> '' then
ous_ids_2 := (select array (
select distinct id from company_companyou where
id = cup.prd_scope_id::int or ous_chain_tree::text LIKE '%$' || cup.prd_scope_id || '>%'
));
end if;
ous_ids := ous_ids_1 || ous_ids_2;
scopes := (select a.id, a.title, b.type from
company_companyou a left outer join company_companyoutype b on a.type_id = b.id
where a.id = any(ous_ids) and b.type = 'Production Scope');
return scopes;
END;
$$ LANGUAGE plpgsql;
select get_managed_user_ids(26);
but I get this error:
[42601] ERROR: invalid type name "setof record"
I googled on the web but I haven't found an example to return a set of rows that fits my needs.
replacing return scopes;
with return next scopes;
or return query scopes;
is not valid.
Anyone can help me on this?
postgresql plpgsql set-returning-functions
postgresql plpgsql set-returning-functions
asked 15 mins ago
LukeLuke
1011
1011
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f230569%2fpostgres-function-returning-set-of-record-error%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 Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f230569%2fpostgres-function-returning-set-of-record-error%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