How to use WITH in a Postgres function?
0
How to write with as in the function? I want to write a recursive function to calculate the weight of a car. It gave me error like :
ERROR: syntax error at or near "WITH"
create or replace function aggregateWeight(p integer)
returns int as
$$
begin
if p in (select p.pid
from parts p) then
return (select p.weight
from parts p)
else
return
WITH RECURSIVE included_parts(pid, sid, quantity) AS (
SELECT ps.pid, ps.sid, ps.quantity
FROM partSubPart ps
UNION ALL
SELECT pr.pid, ps.sid, pr.quantity*ps.quantity
FROM included_parts pr, partSubPart ps
WHERE ps.pid = pr.sid
)
SELECT sum(pr.quantity*p.weight)
FROM included_parts pr, parts p
where pr.sid in (select p.pid from parts p)
GROUP BY pid;
end;
$$ language sql;
postgresql common-table-expression
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
add a comment |
0
How to write with as in the function? I want to write a recursive function to calculate the weight of a car. It gave me error like :
ERROR: syntax error at or near "WITH"
create or replace function aggregateWeight(p integer)
returns int as
$$
begin
if p in (select p.pid
from parts p) then
return (select p.weight
from parts p)
else
return
WITH RECURSIVE included_parts(pid, sid, quantity) AS (
SELECT ps.pid, ps.sid, ps.quantity
FROM partSubPart ps
UNION ALL
SELECT pr.pid, ps.sid, pr.quantity*ps.quantity
FROM included_parts pr, partSubPart ps
WHERE ps.pid = pr.sid
)
SELECT sum(pr.quantity*p.weight)
FROM included_parts pr, parts p
where pr.sid in (select p.pid from parts p)
GROUP BY pid;
end;
$$ language sql;
postgresql common-table-expression
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
RETURN (WITH ... AS (...) SELECT ...);
– 404
Nov 25 '18 at 9:38
You can't useIFin alanguage sqlfunction. You needlanguage plpgsqlfor that.
– a_horse_with_no_name
Nov 25 '18 at 13:21
add a comment |
0
0
0
How to write with as in the function? I want to write a recursive function to calculate the weight of a car. It gave me error like :
ERROR: syntax error at or near "WITH"
create or replace function aggregateWeight(p integer)
returns int as
$$
begin
if p in (select p.pid
from parts p) then
return (select p.weight
from parts p)
else
return
WITH RECURSIVE included_parts(pid, sid, quantity) AS (
SELECT ps.pid, ps.sid, ps.quantity
FROM partSubPart ps
UNION ALL
SELECT pr.pid, ps.sid, pr.quantity*ps.quantity
FROM included_parts pr, partSubPart ps
WHERE ps.pid = pr.sid
)
SELECT sum(pr.quantity*p.weight)
FROM included_parts pr, parts p
where pr.sid in (select p.pid from parts p)
GROUP BY pid;
end;
$$ language sql;
postgresql common-table-expression
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
How to write with as in the function? I want to write a recursive function to calculate the weight of a car. It gave me error like :
ERROR: syntax error at or near "WITH"
create or replace function aggregateWeight(p integer)
returns int as
$$
begin
if p in (select p.pid
from parts p) then
return (select p.weight
from parts p)
else
return
WITH RECURSIVE included_parts(pid, sid, quantity) AS (
SELECT ps.pid, ps.sid, ps.quantity
FROM partSubPart ps
UNION ALL
SELECT pr.pid, ps.sid, pr.quantity*ps.quantity
FROM included_parts pr, partSubPart ps
WHERE ps.pid = pr.sid
)
SELECT sum(pr.quantity*p.weight)
FROM included_parts pr, parts p
where pr.sid in (select p.pid from parts p)
GROUP BY pid;
end;
$$ language sql;
postgresql common-table-expression
postgresql common-table-expression
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
edited Nov 25 '18 at 12:53
Laurenz Albe
47.9k102748
47.9k102748
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
asked Nov 25 '18 at 1:54
J.W.LiuJ.W.Liu
64
64
RETURN (WITH ... AS (...) SELECT ...);
– 404
Nov 25 '18 at 9:38
You can't useIFin alanguage sqlfunction. You needlanguage plpgsqlfor that.
– a_horse_with_no_name
Nov 25 '18 at 13:21
add a comment |
RETURN (WITH ... AS (...) SELECT ...);
– 404
Nov 25 '18 at 9:38
You can't useIFin alanguage sqlfunction. You needlanguage plpgsqlfor that.
– a_horse_with_no_name
Nov 25 '18 at 13:21
RETURN (WITH ... AS (...) SELECT ...);– 404
Nov 25 '18 at 9:38
RETURN (WITH ... AS (...) SELECT ...);– 404
Nov 25 '18 at 9:38
You can't use
IF in a language sql function. You need language plpgsql for that.– a_horse_with_no_name
Nov 25 '18 at 13:21
You can't use
IF in a language sql function. You need language plpgsql for that.– a_horse_with_no_name
Nov 25 '18 at 13:21
add a comment |
1 Answer
1
active
oldest
votes
0
Try to surround the query with parentheses.
You are also missing an END IF; after the ELSE.
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
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
});
}
});
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%2f53464019%2fhow-to-use-with-in-a-postgres-function%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
0
Try to surround the query with parentheses.
You are also missing an END IF; after the ELSE.
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
add a comment |
0
Try to surround the query with parentheses.
You are also missing an END IF; after the ELSE.
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
add a comment |
0
0
0
Try to surround the query with parentheses.
You are also missing an END IF; after the ELSE.
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
Try to surround the query with parentheses.
You are also missing an END IF; after the ELSE.
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
answered Nov 25 '18 at 12:51
Laurenz AlbeLaurenz Albe
47.9k102748
47.9k102748
add a comment |
add a comment |
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%2f53464019%2fhow-to-use-with-in-a-postgres-function%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
RETURN (WITH ... AS (...) SELECT ...);– 404
Nov 25 '18 at 9:38
You can't use
IFin alanguage sqlfunction. You needlanguage plpgsqlfor that.– a_horse_with_no_name
Nov 25 '18 at 13:21