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);

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

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);

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

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);

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

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

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

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

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

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');
});

Post as a guest

Name

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

Name

Required, but never shown

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk