Convert normal sql query to Functions (Postgresql)
up vote
-1
down vote
favorite
How can we convert the below query into functions (Postgresql)
update credit_note_invoice_item c1
set returned_quantity = (select count(c2.id)
from credit_note_invoice_item c2
join invoice_item i1 on i1.id = c2.invoice_item_id
where c1.credit_note_id = c2.credit_note_id and i1.item_id = c1.item_id
group by c2.credit_note_id, i1.item_id),
invoice_item_id = (select min(i2.id)
from invoice_item i2
where i2.invoice_id in (select inv.id
from invoice inv
join credit_note cn on cn.generated_invoice_id = inv.id
where c1.credit_note_id = cn.id)
and i2.item_id = c1.item_id
group by i2.item_id);
sql sql-update query-performance postgresql-9.5
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
asked Nov 20 at 6:05
Gagan Mesala
207
|
show 3 more comments
up vote
-1
down vote
favorite
How can we convert the below query into functions (Postgresql)
update credit_note_invoice_item c1
set returned_quantity = (select count(c2.id)
from credit_note_invoice_item c2
join invoice_item i1 on i1.id = c2.invoice_item_id
where c1.credit_note_id = c2.credit_note_id and i1.item_id = c1.item_id
group by c2.credit_note_id, i1.item_id),
invoice_item_id = (select min(i2.id)
from invoice_item i2
where i2.invoice_id in (select inv.id
from invoice inv
join credit_note cn on cn.generated_invoice_id = inv.id
where c1.credit_note_id = cn.id)
and i2.item_id = c1.item_id
group by i2.item_id);
sql sql-update query-performance postgresql-9.5
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
asked Nov 20 at 6:05
Gagan Mesala
207
question would be why?.
– George Joseph
Nov 20 at 6:09
Performance issue having 5 lakhs records
– Gagan Mesala
Nov 20 at 6:10
Hope all the columns in your where clause are primary keys or have an index on them. Next, did you check the explain plan?
– Namandeep_Kaur
Nov 20 at 6:14
Putting a query into a function won't make that query faster.
– a_horse_with_no_name
Nov 20 at 7:08
@a_horse_with_no_name How can we reduce this query, can you please suggest me best way to execute it.
– Gagan Mesala
Nov 20 at 7:30
|
show 3 more comments
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
How can we convert the below query into functions (Postgresql)
update credit_note_invoice_item c1
set returned_quantity = (select count(c2.id)
from credit_note_invoice_item c2
join invoice_item i1 on i1.id = c2.invoice_item_id
where c1.credit_note_id = c2.credit_note_id and i1.item_id = c1.item_id
group by c2.credit_note_id, i1.item_id),
invoice_item_id = (select min(i2.id)
from invoice_item i2
where i2.invoice_id in (select inv.id
from invoice inv
join credit_note cn on cn.generated_invoice_id = inv.id
where c1.credit_note_id = cn.id)
and i2.item_id = c1.item_id
group by i2.item_id);
sql sql-update query-performance postgresql-9.5
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
asked Nov 20 at 6:05
Gagan Mesala
207
How can we convert the below query into functions (Postgresql)
update credit_note_invoice_item c1
set returned_quantity = (select count(c2.id)
from credit_note_invoice_item c2
join invoice_item i1 on i1.id = c2.invoice_item_id
where c1.credit_note_id = c2.credit_note_id and i1.item_id = c1.item_id
group by c2.credit_note_id, i1.item_id),
invoice_item_id = (select min(i2.id)
from invoice_item i2
where i2.invoice_id in (select inv.id
from invoice inv
join credit_note cn on cn.generated_invoice_id = inv.id
where c1.credit_note_id = cn.id)
and i2.item_id = c1.item_id
group by i2.item_id);
sql sql-update query-performance postgresql-9.5
sql sql-update query-performance postgresql-9.5
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
asked Nov 20 at 6:05
Gagan Mesala
207
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
asked Nov 20 at 6:05
Gagan Mesala
207
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
edited Nov 20 at 7:09
a_horse_with_no_name
290k46443537
290k46443537
asked Nov 20 at 6:05
Gagan Mesala
207
asked Nov 20 at 6:05
Gagan Mesala
207
asked Nov 20 at 6:05
Gagan Mesala
207
207
question would be why?.
– George Joseph
Nov 20 at 6:09
Performance issue having 5 lakhs records
– Gagan Mesala
Nov 20 at 6:10
Hope all the columns in your where clause are primary keys or have an index on them. Next, did you check the explain plan?
– Namandeep_Kaur
Nov 20 at 6:14
Putting a query into a function won't make that query faster.
– a_horse_with_no_name
Nov 20 at 7:08
@a_horse_with_no_name How can we reduce this query, can you please suggest me best way to execute it.
– Gagan Mesala
Nov 20 at 7:30
|
show 3 more comments
question would be why?.
– George Joseph
Nov 20 at 6:09
Performance issue having 5 lakhs records
– Gagan Mesala
Nov 20 at 6:10
Hope all the columns in your where clause are primary keys or have an index on them. Next, did you check the explain plan?
– Namandeep_Kaur
Nov 20 at 6:14
Putting a query into a function won't make that query faster.
– a_horse_with_no_name
Nov 20 at 7:08
@a_horse_with_no_name How can we reduce this query, can you please suggest me best way to execute it.
– Gagan Mesala
Nov 20 at 7:30
question would be why?.
– George Joseph
Nov 20 at 6:09
question would be why?.
– George Joseph
Nov 20 at 6:09
Performance issue having 5 lakhs records
– Gagan Mesala
Nov 20 at 6:10
Performance issue having 5 lakhs records
– Gagan Mesala
Nov 20 at 6:10
Hope all the columns in your where clause are primary keys or have an index on them. Next, did you check the explain plan?
– Namandeep_Kaur
Nov 20 at 6:14
Hope all the columns in your where clause are primary keys or have an index on them. Next, did you check the explain plan?
– Namandeep_Kaur
Nov 20 at 6:14
Putting a query into a function won't make that query faster.
– a_horse_with_no_name
Nov 20 at 7:08
Putting a query into a function won't make that query faster.
– a_horse_with_no_name
Nov 20 at 7:08
@a_horse_with_no_name How can we reduce this query, can you please suggest me best way to execute it.
– Gagan Mesala
Nov 20 at 7:30
@a_horse_with_no_name How can we reduce this query, can you please suggest me best way to execute it.
– Gagan Mesala
Nov 20 at 7:30
|
show 3 more comments
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%2f53387137%2fconvert-normal-sql-query-to-functions-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53387137%2fconvert-normal-sql-query-to-functions-postgresql%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
question would be why?.
– George Joseph
Nov 20 at 6:09
Performance issue having 5 lakhs records
– Gagan Mesala
Nov 20 at 6:10
Hope all the columns in your where clause are primary keys or have an index on them. Next, did you check the explain plan?
– Namandeep_Kaur
Nov 20 at 6:14
Putting a query into a function won't make that query faster.
– a_horse_with_no_name
Nov 20 at 7:08
@a_horse_with_no_name How can we reduce this query, can you please suggest me best way to execute it.
– Gagan Mesala
Nov 20 at 7:30