stackoverflow.com
Open in
urlscan Pro
104.18.32.7
Public Scan
URL:
https://stackoverflow.com/questions/7945932/how-to-return-result-of-a-select-inside-a-function-in-postgresql
Submission: On January 26 via manual from RU — Scanned from DE
Submission: On January 26 via manual from RU — Scanned from DE
Form analysis 2 forms found in the DOM
/search
<form id="search" role="search" action="/search" class="s-topbar--searchbar js-searchbar " autocomplete="off">
<div class="s-topbar--searchbar--input-group">
<input name="q" type="text" role="combobox" placeholder="Search…" value="" autocomplete="off" maxlength="240" class="s-input s-input__search js-search-field " aria-label="Search" aria-controls="top-search" data-controller="s-popover"
data-action="focus->s-popover#show" data-s-popover-placement="bottom-start" aria-expanded="false">
<svg aria-hidden="true" class="s-input-icon s-input-icon__search svg-icon iconSearch" width="18" height="18" viewBox="0 0 18 18">
<path d="m18 16.5-5.14-5.18h-.35a7 7 0 1 0-1.19 1.19v.35L16.5 18l1.5-1.5ZM12 7A5 5 0 1 1 2 7a5 5 0 0 1 10 0Z"></path>
</svg>
<div class="s-popover p0 wmx100 wmn4 sm:wmn-initial js-top-search-popover" id="top-search" role="menu">
<div class="s-popover--arrow"></div>
<div class="js-spinner p24 d-flex ai-center jc-center d-none">
<div class="s-spinner s-spinner__sm fc-orange-400">
<div class="v-visible-sr">Loading…</div>
</div>
</div>
<span class="v-visible-sr js-screen-reader-info"></span>
<div class="js-ac-results overflow-y-auto hmx3 d-none"></div>
<div class="js-search-hints" aria-describedby="Tips for searching"></div>
</div>
</div>
</form>POST /questions/7945932/answer/submit
<form id="post-form" action="/questions/7945932/answer/submit" method="post" class="js-add-answer-component post-form">
<input type="hidden" id="post-id" value="7945932">
<input type="hidden" id="qualityBanWarningShown" name="qualityBanWarningShown" value="false">
<input type="hidden" name="referrer" value="">
<h2 class="space" id="your-answer-header"> Your Answer </h2>
<script>
StackExchange.ifUsing("editor", function() {
StackExchange.using("externalEditor", function() {
StackExchange.using("snippets", function() {
StackExchange.snippets.init();
});
});
}, "code-snippets");
</script>
<script>
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({
useStacksEditor: false,
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by \u003ca href=\"https://imgur.com/\"\u003e\u003csvg class=\"svg-icon\" width=\"50\" height=\"18\" viewBox=\"0 0 50 18\" fill=\"none\" xmlns=\"http://www.w3.org/2000/svg\"\u003e\u003ctitle\u003eImgur Logo\u003c/title\u003e\u003cpath d=\"M46.1709 9.17788C46.1709 8.26454 46.2665 7.94324 47.1084 7.58816C47.4091 7.46349 47.7169 7.36433 48.0099 7.26993C48.9099 6.97997 49.672 6.73443 49.672 5.93063C49.672 5.22043 48.9832 4.61182 48.1414 4.61182C47.4335 4.61182 46.7256 4.91628 46.0943 5.50789C45.7307 4.9328 45.2525 4.66231 44.6595 4.66231C43.6264 4.66231 43.1481 5.28821 43.1481 6.59048V11.9512C43.1481 13.2535 43.6264 13.8962 44.6595 13.8962C45.6924 13.8962 46.1709 13.2535 46.1709 11.9512V9.17788Z\"/\u003e\u003cpath d=\"M32.492 10.1419C32.492 12.6954 34.1182 14.0484 37.0451 14.0484C39.9723 14.0484 41.5985 12.6954 41.5985 10.1419V6.59049C41.5985 5.28821 41.1394 4.66232 40.1061 4.66232C39.0732 4.66232 38.5948 5.28821 38.5948 6.59049V9.60062C38.5948 10.8521 38.2696 11.5455 37.0451 11.5455C35.8209 11.5455 35.4954 10.8521 35.4954 9.60062V6.59049C35.4954 5.28821 35.0173 4.66232 34.0034 4.66232C32.9703 4.66232 32.492 5.28821 32.492 6.59049V10.1419Z\" /\u003e\u003cpath fill-rule=\"evenodd\" clip-rule=\"evenodd\" d=\"M25.6622 17.6335C27.8049 17.6335 29.3739 16.9402 30.2537 15.6379C30.8468 14.7755 30.9615 13.5579 30.9615 11.9512V6.59049C30.9615 5.28821 30.4833 4.66231 29.4502 4.66231C28.9913 4.66231 28.4555 4.94978 28.1109 5.50789C27.499 4.86533 26.7335 4.56087 25.7005 4.56087C23.1369 4.56087 21.0134 6.57349 21.0134 9.27932C21.0134 11.9852 23.003 13.913 25.3754 13.913C26.5612 13.913 27.4607 13.4902 28.1109 12.6616C28.1109 12.7229 28.1161 12.7799 28.121 12.8346C28.1256 12.8854 28.1301 12.9342 28.1301 12.983C28.1301 14.4373 27.2502 15.2321 25.777 15.2321C24.8349 15.2321 24.1352 14.9821 23.5661 14.7787C23.176 14.6393 22.8472 14.5218 22.5437 14.5218C21.7977 14.5218 21.2429 15.0123 21.2429 15.6887C21.2429 16.7375 22.9072 17.6335 25.6622 17.6335ZM24.1317 9.27932C24.1317 7.94324 24.9928 7.09766 26.1024 7.09766C27.2119 7.09766 28.0918 7.94324 28.0918 9.27932C28.0918 10.6321 27.2311 11.5116 26.1024 11.5116C24.9737 11.5116 24.1317 10.6491 24.1317 9.27932Z\"/\u003e\u003cpath d=\"M16.8045 11.9512C16.8045 13.2535 17.2637 13.8962 18.2965 13.8962C19.3298 13.8962 19.8079 13.2535 19.8079 11.9512V8.12928C19.8079 5.82936 18.4879 4.62866 16.4027 4.62866C15.1594 4.62866 14.279 4.98375 13.3609 5.88013C12.653 5.05154 11.6581 4.62866 10.3573 4.62866C9.34336 4.62866 8.57809 4.89931 7.9466 5.5079C7.58314 4.9328 7.10506 4.66232 6.51203 4.66232C5.47873 4.66232 5.00066 5.28821 5.00066 6.59049V11.9512C5.00066 13.2535 5.47873 13.8962 6.51203 13.8962C7.54479 13.8962 8.0232 13.2535 8.0232 11.9512V8.90741C8.0232 7.58817 8.44431 6.91179 9.53458 6.91179C10.5104 6.91179 10.893 7.58817 10.893 8.94108V11.9512C10.893 13.2535 11.3711 13.8962 12.4044 13.8962C13.4375 13.8962 13.9157 13.2535 13.9157 11.9512V8.90741C13.9157 7.58817 14.3365 6.91179 15.4269 6.91179C16.4027 6.91179 16.8045 7.58817 16.8045 8.94108V11.9512Z\"/\u003e\u003cpath d=\"M3.31675 6.59049C3.31675 5.28821 2.83866 4.66232 1.82471 4.66232C0.791758 4.66232 0.313354 5.28821 0.313354 6.59049V11.9512C0.313354 13.2535 0.791758 13.8962 1.82471 13.8962C2.85798 13.8962 3.31675 13.2535 3.31675 11.9512V6.59049Z\" /\u003e\u003cpath d=\"M1.87209 0.400291C0.843612 0.400291 0 1.1159 0 1.98861C0 2.87869 0.822846 3.57676 1.87209 3.57676C2.90056 3.57676 3.7234 2.87869 3.7234 1.98861C3.7234 1.1159 2.90056 0.400291 1.87209 0.400291Z\" fill=\"#1BB76E\"/\u003e\u003c/svg\u003e\u003c/a\u003e",
contentPolicyHtml: "User contributions licensed under \u003ca href=\"https://stackoverflow.com/help/licensing\"\u003eCC BY-SA\u003c/a\u003e \u003ca href=\"https://stackoverflow.com/legal/acceptable-use-policy\"\u003e(content policy)\u003c/a\u003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer",
enableTables: true,
isStacksEditorPreviewEnabled: false,
enableTables: true,
enableSnippets: true
});
}
});
</script>
<div id="post-editor" class="post-editor js-post-editor">
<div class="ps-relative">
<div class="wmd-container mb8">
<div id="wmd-button-bar" class="wmd-button-bar btr-sm">
<ul id="wmd-button-row" class="wmd-button-row">
<li id="wmd-bold-button" class="wmd-button" style="left: 0px;"><span style="background-position: 0px -20px;"></span></li>
<li id="wmd-italic-button" class="wmd-button" style="left: 25px;"><span style="background-position: -20px -20px;"></span></li>
<li id="wmd-spacer1" class="wmd-spacer" style="left: 50px;"><span style="background-position: -40px -20px;"></span></li>
<li id="wmd-link-button" class="wmd-button" style="left: 75px;"><span style="background-position: -40px -20px;"></span></li>
<li id="wmd-quote-button" class="wmd-button" style="left: 100px;"><span style="background-position: -60px -20px;"></span></li>
<li id="wmd-code-button" class="wmd-button" style="left: 125px;"><span style="background-position: -80px -20px;"></span></li>
<li id="wmd-image-button" class="wmd-button" style="left: 150px;"><span style="background-position: -100px -20px;"></span></li>
<li id="wmd-spacer2" class="wmd-spacer" style="left: 175px;"><span style="background-position: -120px -20px;"></span></li>
<li id="wmd-olist-button" class="wmd-button" style="left: 200px;"><span style="background-position: -120px -20px;"></span></li>
<li id="wmd-ulist-button" class="wmd-button" style="left: 225px;"><span style="background-position: -140px -20px;"></span></li>
<li id="wmd-heading-button" class="wmd-button" style="left: 250px;"><span style="background-position: -160px -20px;"></span></li>
<li id="wmd-hr-button" class="wmd-button" style="left: 275px;"><span style="background-position: -180px -20px;"></span></li>
<li id="wmd-spacer3" class="wmd-spacer" style="left: 300px;"><span style="background-position: -200px -20px;"></span></li>
<li id="wmd-undo-button" class="wmd-button" style="left: 325px;"><span style="background-position: -200px -20px;"></span></li>
<li id="wmd-redo-button" class="wmd-button" style="left: 350px;"><span style="background-position: -220px -20px;"></span></li>
<li class="wmd-spacer wmd-spacer-max"></li>
</ul>
</div>
<div class="ai-content-policy-notice js-ai-policy-notice fc-black p8 bl br bc-black-300 d-none" aria-hidden="true">
<div class="d-flex jc-space-between ac-center gsx gs2">
<p class="flex--item as-center"><b>Reminder:</b> Answers generated by artificial intelligence tools are not allowed on Stack Overflow. <a href="/help/ai-policy">Learn more</a></p>
<button class="flex--item js-dismiss-ai-banner s-btn s-btn__sm s-btn__icon fc-black"><svg aria-hidden="true" class="svg-icon iconClearSm" width="14" height="14" viewBox="0 0 14 14">
<path d="M12 3.41 10.59 2 7 5.59 3.41 2 2 3.41 5.59 7 2 10.59 3.41 12 7 8.41 10.59 12 12 10.59 8.41 7 12 3.41Z"></path>
</svg></button>
</div>
</div>
<input type="hidden" name="AIPolicyNoticeShown" value="true">
<div class="js-stacks-validation">
<div class="ps-relative">
<textarea id="wmd-input" name="post-text" class="wmd-input s-input bar0 js-post-body-field" data-editor-type="wmd" data-post-type-id="2" cols="92" rows="15" aria-labelledby="your-answer-header" tabindex="101"
data-min-length=""></textarea>
</div>
<div class="s-input-message mt4 d-none js-stacks-validation-message"></div>
</div>
</div>
</div>
<aside class="d-flex ai-start jc-space-between js-answer-help s-notice s-notice__warning pb0 pr4 pt4 mb8 d-none" role="status" aria-hidden="true">
<div class="flex--item pt8">
<p>Thanks for contributing an answer to Stack Overflow!</p>
<ul>
<li>Please be sure to <em>answer the question</em>. Provide details and share your research!</li>
</ul>
<p>But <em>avoid</em> …</p>
<ul>
<li>Asking for help, clarification, or responding to other answers.</li>
<li>Making statements based on opinion; back them up with references or personal experience.</li>
</ul>
<p>To learn more, see our <a href="/help/how-to-answer">tips on writing great answers</a>.</p>
</div>
<button class="flex--item js-answer-help-close-btn s-btn s-btn__muted fc-black-600">
<svg aria-hidden="true" class="svg-icon iconClear" width="18" height="18" viewBox="0 0 18 18">
<path d="M15 4.41 13.59 3 9 7.59 4.41 3 3 4.41 7.59 9 3 13.59 4.41 15 9 10.41 13.59 15 15 13.59 10.41 9 15 4.41Z"></path>
</svg>
</button>
</aside>
<div>
<div id="draft-saved" class="fc-success h24" style="display:none;">Draft saved</div>
<div id="draft-discarded" class="fc-error h24" style="display:none;">Draft discarded</div>
</div>
<div id="wmd-preview" class="s-prose mb16 wmd-preview js-wmd-preview"></div>
<div></div>
<div class="edit-block">
<input id="fkey" name="fkey" type="hidden" value="9c6a9188acbbdafd3f5004ddfcb4a17cf5d056c3d11a30c6b1af727e13e7022f">
<input id="author" name="author" type="text">
</div>
</div>
<div class="ps-relative">
<div class="form-item new-post-login p0 my16">
<div class="d-flex gs16 md:fd-column new-login-form">
<div class="d-flex fd-column w50 md:w-auto gsy gs8 jc-space-between new-login-left">
<h3 class="flex--item fs-title">Sign up or
<a id="login-link" href="/users/login?ssrc=question_page&returnurl=https%3a%2f%2fstackoverflow.com%2fquestions%2f7945932%2fhow-to-return-result-of-a-select-inside-a-function-in-postgresql%23new-answer">log in</a></h3>
<script>
StackExchange.ready(function() {
StackExchange.helpers.onClickDraftSave('#login-link');
});
</script>
<div class="flex--item s-btn s-btn__muted s-btn__outlined s-btn__icon google-login" data-ga="["sign up","Sign Up Started - Google","New Post",null,null]">
<svg aria-hidden="true" class="native svg-icon iconGoogle" width="18" height="18" viewBox="0 0 18 18">
<path fill="#4285F4" d="M16.51 8H8.98v3h4.3c-.18 1-.74 1.48-1.6 2.04v2.01h2.6a7.8 7.8 0 0 0 2.38-5.88c0-.57-.05-.66-.15-1.18Z"></path>
<path fill="#34A853" d="M8.98 17c2.16 0 3.97-.72 5.3-1.94l-2.6-2a4.8 4.8 0 0 1-7.18-2.54H1.83v2.07A8 8 0 0 0 8.98 17Z"></path>
<path fill="#FBBC05" d="M4.5 10.52a4.8 4.8 0 0 1 0-3.04V5.41H1.83a8 8 0 0 0 0 7.18l2.67-2.07Z"></path>
<path fill="#EA4335" d="M8.98 4.18c1.17 0 2.23.4 3.06 1.2l2.3-2.3A8 8 0 0 0 1.83 5.4L4.5 7.49a4.77 4.77 0 0 1 4.48-3.3Z"></path>
</svg> Sign up using Google
</div>
<div class="flex--item s-btn s-btn__muted s-btn__icon facebook-login" data-ga="["sign up","Sign Up Started - Facebook","New Post",null,null]">
<svg aria-hidden="true" class="svg-icon iconFacebook" width="18" height="18" viewBox="0 0 18 18">
<path fill="#4167B2"
d="M3 1a2 2 0 0 0-2 2v12c0 1.1.9 2 2 2h12a2 2 0 0 0 2-2V3a2 2 0 0 0-2-2H3Zm6.55 16v-6.2H7.46V8.4h2.09V6.61c0-2.07 1.26-3.2 3.1-3.2.88 0 1.64.07 1.87.1v2.16h-1.29c-1 0-1.19.48-1.19 1.18V8.4h2.39l-.31 2.42h-2.08V17h-2.5Z"></path>
</svg> Sign up using Facebook
</div>
<div class="flex--item s-btn s-btn__muted s-btn__outlined s-btn__icon stackexchange-login" data-ga="["sign up","Sign Up Navigation","New Post",null,null]">
<svg aria-hidden="true" class="native svg-icon iconLogoGlyphXSm" width="18" height="18" viewBox="0 0 18 18">
<path d="M14 16v-5h2v7H2v-7h2v5h10Z" fill="#BCBBBB"></path>
<path d="m12.09.72-1.21.9 4.5 6.07 1.22-.9L12.09.71ZM5 15h8v-2H5v2Zm9.15-5.87L8.35 4.3l.96-1.16 5.8 4.83-.96 1.16Zm-7.7-1.47 6.85 3.19.63-1.37-6.85-3.2-.63 1.38Zm6.53 5L5.4 11.39l.38-1.67 7.42 1.48-.22 1.46Z" fill="#F48024"></path>
</svg> Sign up using Email and Password
</div>
</div>
<input type="hidden" name="use-facebook" class="use-facebook" value="false">
<input type="hidden" name="use-google" class="use-google" value="false">
<button type="button" class="d-none js-submit-openid">Submit</button>
<div class="d-flex gsy gs8 fd-column w50 md:w-auto new-login-right form-item p0">
<h3 class="flex--item fs-title">Post as a guest</h3>
<div class="flex--item">
<div class="d-flex gs4 gsy fd-column">
<label class="s-label" for="display-name">Name</label>
<div class="d-flex ps-relative">
<input class="s-input" id="display-name" name="display-name" maxlength="30" type="text" value="" tabindex="105" placeholder="">
</div>
</div>
</div>
<div class="flex--item">
<div class="d-flex gs4 gsy fd-column">
<div class="flex--item">
<div class="d-flex gs2 gsy fd-column">
<label class="flex--item s-label" for="m-address">Email</label>
<p class="flex--item s-description">Required, but never shown</p>
</div>
</div>
<div class="d-flex ps-relative">
<input class="s-input js-post-email-field" id="m-address" name="m-address" type="text" value="" size="40" tabindex="106" placeholder="">
</div>
</div>
</div>
</div>
</div>
</div>
<script>
StackExchange.ready(function() {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f7945932%2fhow-to-return-result-of-a-select-inside-a-function-in-postgresql%23new-answer', 'question_page');
});
</script>
<noscript>
<h3 class="flex--item fs-title">Post as a guest</h3>
<div class="flex--item">
<div class="d-flex gs4 gsy fd-column">
<label class="s-label" for="display-name">Name</label>
<div class="d-flex ps-relative">
<input class="s-input" id="display-name" name="display-name" maxlength="30" type="text" value="" tabindex="105" placeholder="">
</div>
</div>
</div>
<div class="flex--item">
<div class="d-flex gs4 gsy fd-column">
<div class="flex--item">
<div class="d-flex gs2 gsy fd-column">
<label class="flex--item s-label" for="m-address">Email</label>
<p class="flex--item s-description">Required, but never shown</p>
</div>
</div>
<div class="d-flex ps-relative">
<input class="s-input js-post-email-field" id="m-address" name="m-address" type="text" value="" size="40" tabindex="106" placeholder="">
</div>
</div>
</div>
</noscript>
</div>
<div class="form-submit clear-both d-flex gsx gs4">
<button id="submit-button" class="flex--item s-btn s-btn__filled s-btn__icon" type="submit" tabindex="120" autocomplete="off"> Post Your Answer </button>
<button class="flex--item s-btn s-btn__danger discard-answer dno"> Discard </button>
<p class="privacy-policy-agreement"> By clicking “Post Your Answer”, you agree to our <a href="https://stackoverflow.com/legal/terms-of-service/public" name="tos" target="_blank" class="-link">terms of service</a> and acknowledge you have read
our <a href="https://stackoverflow.com/legal/privacy-policy" name="privacy" target="_blank" class="-link">privacy policy</a>.<input type="hidden" name="legalLinksShown" value="1">
</p>
</div>
<div class="js-general-error general-error clear-both d-none" aria-live="polite"></div>
</form>Text Content
Stack Overflow
1. About
2. Products
3. For Teams
1. Stack Overflow Public questions & answers
2. Stack Overflow for Teams Where developers & technologists share private
knowledge with coworkers
3. Talent Build your employer brand
4. Advertising Reach developers & technologists worldwide
5. Labs The future of collective knowledge sharing
6. About the company
Loading…
1. CURRENT COMMUNITY
*
Stack Overflow
help chat
*
Meta Stack Overflow
YOUR COMMUNITIES
Sign up or log in to customize your list.
MORE STACK EXCHANGE COMMUNITIES
company blog
2.
3. Log in
4. Sign up
1. 1. Home
2. Questions
3. Tags
4.
5. Users
6. Companies
7. Collectives
8. Explore Collectives
9. Labs
10. Discussions
2. Teams
Stack Overflow for Teams – Start collaborating and sharing organizational
knowledge. Create a free Team Why Teams?
3. Teams
4. Create free Team
COLLECTIVES™ ON STACK OVERFLOW
Find centralized, trusted content and collaborate around the technologies you
use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy
to search.
Learn more about Teams
Get early access and see previews of new features.
Learn more about Labs
HOW TO RETURN RESULT OF A SELECT INSIDE A FUNCTION IN POSTGRESQL?
Ask Question
Asked 12 years, 3 months ago
Modified 1 year, 5 months ago
Viewed 315k times
Report this ad
This question shows research effort; it is useful and clear
168
This question does not show any research effort; it is unclear or not useful
Save this question.
Show activity on this post.
I have this function in PostgreSQL, but I don't know how to return the result of
the query:
CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
RETURNS SETOF RECORD AS
$$
BEGIN
SELECT text, count(*), 100 / maxTokens * count(*)
FROM (
SELECT text
FROM token
WHERE chartype = 'ALPHABETIC'
LIMIT maxTokens
) as tokens
GROUP BY text
ORDER BY count DESC
END
$$
LANGUAGE plpgsql;
But I don't know how to return the result of the query inside the PostgreSQL
function.
I found that the return type should be SETOF RECORD, right? But the return
command is not right.
What is the right way to do this?
* sql
* postgresql
* return
* plpgsql
* return-type
Share
Share a link to this question
Copy linkCC BY-SA 3.0
Improve this question
Follow
Follow this question to receive notifications
edited Feb 11, 2014 at 22:59
Erwin Brandstetter
622k149149 gold badges11021102 silver badges12491249 bronze badges
asked Oct 30, 2011 at 15:47
Renato DinhaniRenato Dinhani
35.5k5555 gold badges139139 silver badges201201 bronze badges
2
* Why do you count them; do you have duplicate tokens in your token TABLE?
Also: please add the table definition to your question.
– wildplasser
Oct 30, 2011 at 15:52
* 1
Is this your entire function? If you don't have any other statements in the
function, you should just make it LANGUAGE SQL.
– jpmc26
Oct 4, 2014 at 18:38
Add a comment |
2 ANSWERS 2
Sorted by: Reset to default
Highest score (default) Trending (recent votes count more) Date modified (newest
first) Date created (oldest first)
This answer is useful
245
This answer is not useful
Save this answer.
+500
This answer has been awarded bounties worth 500 reputation by Community
Show activity on this post.
Use RETURN QUERY:
CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
RETURNS TABLE (txt text -- also visible as OUT param in function body
, cnt bigint
, ratio bigint)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY
SELECT t.txt
, count(*) AS cnt -- column alias only visible in this query
, (count(*) * 100) / _max_tokens -- I added parentheses
FROM (
SELECT t.txt
FROM token t
WHERE t.chartype = 'ALPHABETIC'
LIMIT _max_tokens
) t
GROUP BY t.txt
ORDER BY cnt DESC; -- potential ambiguity
END
$func$;
Call:
SELECT * FROM word_frequency(123);
Defining the return type explicitly is much more practical than returning a
generic record. This way you don't have to provide a column definition list with
every function call. RETURNS TABLE is one way to do that. There are others. Data
types of OUT parameters have to match exactly what is returned by the query.
Choose names for OUT parameters carefully. They are visible in the function body
almost anywhere. Table-qualify columns of the same name to avoid conflicts or
unexpected results. I did that for all columns in my example.
But note the potential naming conflict between the OUT parameter cnt and the
column alias of the same name. In this particular case (RETURN QUERY SELECT ...)
Postgres uses the column alias over the OUT parameter either way. This can be
ambiguous in other contexts, though. There are various ways to avoid any
confusion:
1. Use the ordinal position of the item in the SELECT list: ORDER BY 2 DESC.
Example:
* Select first row in each GROUP BY group?
2. Repeat the expression ORDER BY count(*).
3. (Not required here.) Set the configuration parameter
plpgsql.variable_conflict or use the special command #variable_conflict
error | use_variable | use_column in the function. See:
* Naming conflict between function parameter and result of JOIN with USING
clause
Don't use "text" or "count" as column names. Both are legal to use in Postgres,
but "count" is a reserved word in standard SQL and a basic function name and
"text" is a basic data type. Can lead to confusing errors. I use txt and cnt in
my examples, you may want more explicit names.
Added a missing ; and corrected a syntax error in the header. (_max_tokens int),
not (int maxTokens) - data type after name.
While working with integer division, it's better to multiply first and divide
later, to minimize the rounding error. Or work with numeric or a floating point
type. See below.
ALTERNATIVE
This is what I think your query should actually look like (calculating a
relative share per token):
CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
RETURNS TABLE (txt text
, abs_cnt bigint
, relative_share numeric)
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY
SELECT t.txt, t.cnt
, round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2) -- AS relative_share
FROM (
SELECT t.txt, count(*) AS cnt
FROM token t
WHERE t.chartype = 'ALPHABETIC'
GROUP BY t.txt
ORDER BY cnt DESC
LIMIT _max_tokens
) t
ORDER BY t.cnt DESC;
END
$func$;
The expression sum(t.cnt) OVER () is a window function. You could use a CTE
instead of the subquery. Pretty, but a subquery is typically cheaper in simple
cases like this one (mostly before Postgres 12).
A final explicit RETURN statement is not required (but allowed) when working
with OUT parameters or RETURNS TABLE (which makes implicit use of OUT
parameters).
round() with two parameters only works for numeric types. count() in the
subquery produces a bigint result and a sum() over this bigint produces a
numeric result, thus we deal with a numeric number automatically and everything
just falls into place.
Share
Share a link to this answer
Copy linkCC BY-SA 4.0
Improve this answer
Follow
Follow this answer to receive notifications
edited May 20, 2022 at 21:34
answered Oct 30, 2011 at 15:50
Erwin BrandstetterErwin Brandstetter
622k149149 gold badges11021102 silver badges12491249 bronze badges
6
* Very thanks to your answer and corrections. Is working fine now (I only
changed the ratio type to numeric).
– Renato Dinhani
Oct 30, 2011 at 16:38
* @RenatoDinhaniConceição Cool! I added a version that may or may not answer an
additional question that you haven't actually asked. ;)
– Erwin Brandstetter
Oct 30, 2011 at 16:52
* Nice, the only thing is I think you need a RETURN; before that END;, at least
I did - but I'm doing a UNION so I'm not sure if that makes it different.
– yekta
Sep 4, 2013 at 15:08
* @yekta: I added some information concerning the role of RETURN. Fixed an
unrelated error and added some improvements while being at it.
– Erwin Brandstetter
Sep 4, 2013 at 18:12
* 2
What is the way to do this when you don't want to constrain what is in Return
TABLE(). IE RETURN TABLE(*) ?
– Nick
Apr 9, 2015 at 1:19
| Show 1 more comment
This answer is useful
9
This answer is not useful
Save this answer.
Show activity on this post.
Please see the following link for documentation:
https://www.postgresql.org/docs/current/xfunc-sql.html
Example:
CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;
Share
Share a link to this answer
Copy linkCC BY-SA 4.0
Improve this answer
Follow
Follow this answer to receive notifications
edited Aug 29, 2022 at 7:20
Lee Goddard
10.9k44 gold badges4747 silver badges6464 bronze badges
answered Jun 26, 2019 at 7:09
Moumita DasMoumita Das
9911 silver badge11 bronze badge
3
* Yes, better to use "pure SQL" whenever you can. You can use two or more
commands (SELECT's, INSERTS, etc.), only the last one is the return value. A
workaround for procedural "dependent step by step" is to use a chain of
clauses in a WITH. For example WITH t1 as (SELECT etc1), t2 as (SELECT etc2
from t1) SELECT result FROM t2;
– Peter Krauss
May 3, 2021 at 0:06
* @PeterKrauss may I ask why? Are there any references recommending SQL over
PLPGSQL?
– Rafs
Jun 9, 2023 at 11:38
* Hi @Rafs, yes... It's not easy to find exactly what PostgreSQL does nowadays,
but is the JIT optimizer that, for example, reuse SQL code in a SQL-VIEW,
postgresql.org/docs/current/jit-reason.html
– Peter Krauss
Jun 9, 2023 at 12:26
Add a comment |
YOUR ANSWER
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
Reminder: Answers generated by artificial intelligence tools are not allowed on
Stack Overflow. Learn more
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Submit
POST AS A GUEST
Name
Email
Required, but never shown
POST AS A GUEST
Name
Email
Required, but never shown
Post Your Answer Discard
By clicking “Post Your Answer”, you agree to our terms of service and
acknowledge you have read our privacy policy.
NOT THE ANSWER YOU'RE LOOKING FOR? BROWSE OTHER QUESTIONS TAGGED
* SQL
* POSTGRESQL
* RETURN
* PLPGSQL
* RETURN-TYPE
OR ASK YOUR OWN QUESTION.
* Featured on Meta
* Updates to the Acceptable Use Policy (AUP) – January 2024
* Site maintenance - Thursday, February 1, 2024 @ 01:00 UTC (Wednesday,
January...
* January 2024 post from Ryan Polk, Chief Product Officer
* Temporary policy: Generative AI (e.g., ChatGPT) is banned
* Discussions update: Expansion to all tags in February
Report this ad
Report this ad
20 people chatting
SQL
2 days ago - WhatsThePoint
LINKED
1989
Select first row in each GROUP BY group?
57
PostgreSQL: ERROR: 42601: a column definition list is required for functions
returning "record"
42
Refactor a PL/pgSQL function to return the output of various SELECT queries
14
How to set value of composite variable field using dynamic SQL
17
Return SETOF rows from PostgreSQL function
10
How to get result set from PostgreSQL stored procedure?
4
Accessing the Return Table in a Postgres Function
2
Alternatives to WITH .. AS .. clause in PostgreSQL
2
In plpgsql, return set of composite type without cast?
0
INSERT INTO ... RETURNING - ambiguous column reference
See more linked questions
RELATED
3
Return a query from a function?
5
Return a select from a plpgsql function
3
Returning results from a function in 'select statement' format
0
PostgreSQL:How to return result of a SELECT statement within a function using
PostgreSQL?
2
How do I return the result of a RETURNING clause from a plpgsql function?
1
Return result of query from function in postgresql
1
Return a record from function
0
How Do I use or properly return a value from my PostgreSQL function?
0
How to return the contents of another plpgsql function?
1
Postgres function returning a string from a select statement
HOT NETWORK QUESTIONS
*
Crew of soldiers learn that robots have been faking the war in an effort to
save humanity
*
How fast is too fast for travel inside the solar system?
*
Why does the Islamic Republic of Iran view the US as its principal
geopolitical adversary in the world?
*
Why do so many names end with -us?
*
How do cosmonauts board the Soyuz spacecraft?
*
Probability that sum of binary variables is even
*
Make a projected image from the camera follow the object orthographically
*
How do Yubikeys improve security if I am typically also forced to enable
other, weaker 2FA methods?
*
2-litre pack of duty-free spirits, but the limit is 1 litre
*
using bash, create files based on pattern match
*
What is the earliest known example of triplets?
*
Will courts interpret words to mean the opposite of what they actually mean
if that is clearly the author's intent?
*
Overload resolution and template argument deduction - why is 0 special?
*
How to reach Southampton cruise port from London airports or Eurostar
stations and vice-versa, without breaking the bank?
*
Definitions of coercivity - functional analysis
*
Some .oints and numbers
*
Find the finest building blocks
*
Do full-text translators such as DeepL or Google Translate fall under the
term "Generative AI"?
*
Same MOSFET component but reversed arrow
*
Black and white keyboard keys
*
Which pin is pin #1?
*
Hartree potential term expression when solving the radial Schrodinger
equation
*
What does "Catholic privilege" mean in Pym's Excellent Women?
*
How to get flight schedules data?
Question feed
SUBSCRIBE TO RSS
Question feed
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
lang-sql
STACK OVERFLOW
* Questions
* Help
PRODUCTS
* Teams
* Advertising
* Collectives
* Talent
COMPANY
* About
* Press
* Work Here
* Legal
* Privacy Policy
* Terms of Service
* Contact Us
* Cookie Settings
* Cookie Policy
STACK EXCHANGE NETWORK
* Technology
* Culture & recreation
* Life & arts
* Science
* Professional
* Business
* API
* Data
* Blog
* Facebook
* Twitter
* LinkedIn
* Instagram
Site design / logo © 2024 Stack Exchange Inc; user contributions licensed under
CC BY-SA. rev 2024.1.26.3951
WE CARE ABOUT YOUR PRIVACY
We and our 4 partners store and/or access information on a device, such as
unique IDs in cookies to process personal data. You may accept or manage your
choices by clicking below or at any time in the privacy policy page. These
choices will be signaled to our partners and will not affect browsing
data.Cookie Policy
WE AND OUR PARTNERS PROCESS DATA TO PROVIDE:
Store and/or access information on a device. Personalised advertising and
content, advertising and content measurement, audience research and services
development. List of Partners (vendors)
Accept all cookies Necessary cookies only
Customize Settings