Postgres cursor targets
up vote
0
down vote
favorite
I would appreciate advice on best practices to create a pg9.6 structure to hold multiple rows from multiple joined tables as a cursor target. The docs were not clear on this, other searches seemed to indicate using temp table, such as below.
DECLARE
mycursor refcursor;
BEGIN
CREATE TEMPORARY TABLE tmp_persons (
personid INTEGER,
primaryconnect INTEGER
);
OPEN mycursor FOR SELECT p.personid,c.primaryconnect FROM tpersons p JOIN tconnections c ON .....
LOOP
FETCH mycursor INTO tmp_persons;
.. do something using tmp_persons.personid, tmp_persons.primaryconnect
postgresql plpgsql postgres-9.6
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
asked Nov 9 at 22:56
user542319
1881314
add a comment |
up vote
0
down vote
favorite
I would appreciate advice on best practices to create a pg9.6 structure to hold multiple rows from multiple joined tables as a cursor target. The docs were not clear on this, other searches seemed to indicate using temp table, such as below.
DECLARE
mycursor refcursor;
BEGIN
CREATE TEMPORARY TABLE tmp_persons (
personid INTEGER,
primaryconnect INTEGER
);
OPEN mycursor FOR SELECT p.personid,c.primaryconnect FROM tpersons p JOIN tconnections c ON .....
LOOP
FETCH mycursor INTO tmp_persons;
.. do something using tmp_persons.personid, tmp_persons.primaryconnect
postgresql plpgsql postgres-9.6
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
asked Nov 9 at 22:56
user542319
1881314
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I would appreciate advice on best practices to create a pg9.6 structure to hold multiple rows from multiple joined tables as a cursor target. The docs were not clear on this, other searches seemed to indicate using temp table, such as below.
DECLARE
mycursor refcursor;
BEGIN
CREATE TEMPORARY TABLE tmp_persons (
personid INTEGER,
primaryconnect INTEGER
);
OPEN mycursor FOR SELECT p.personid,c.primaryconnect FROM tpersons p JOIN tconnections c ON .....
LOOP
FETCH mycursor INTO tmp_persons;
.. do something using tmp_persons.personid, tmp_persons.primaryconnect
postgresql plpgsql postgres-9.6
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
asked Nov 9 at 22:56
user542319
1881314
I would appreciate advice on best practices to create a pg9.6 structure to hold multiple rows from multiple joined tables as a cursor target. The docs were not clear on this, other searches seemed to indicate using temp table, such as below.
DECLARE
mycursor refcursor;
BEGIN
CREATE TEMPORARY TABLE tmp_persons (
personid INTEGER,
primaryconnect INTEGER
);
OPEN mycursor FOR SELECT p.personid,c.primaryconnect FROM tpersons p JOIN tconnections c ON .....
LOOP
FETCH mycursor INTO tmp_persons;
.. do something using tmp_persons.personid, tmp_persons.primaryconnect
postgresql plpgsql postgres-9.6
postgresql plpgsql postgres-9.6
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
asked Nov 9 at 22:56
user542319
1881314
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
asked Nov 9 at 22:56
user542319
1881314
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
edited Nov 11 at 8:57
a_horse_with_no_name
290k46439535
290k46439535
asked Nov 9 at 22:56
user542319
1881314
asked Nov 9 at 22:56
user542319
1881314
asked Nov 9 at 22:56
user542319
1881314
1881314
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I hope so documentation is clean.
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
So you cannot to use temporary table as target. Use record variable if there are not any special requirement:
DECLARE
r record;
mycursor refcursor;
BEGIN
OPEN mycursor ..
LOOP
FETCH mycursor INTO r;
EXIST WHEN NOT FOUND;
RAISE NOTICE 'personid: %', r.personid;
For this simple task is better to use FOR IN SELECT statement.
DECLARE r record;
BEGIN
FOR r IN SELECT p.personid, ...
LOOP
RAISE NOTICE 'personid: %', r.personid;
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
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',
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
});
}
});
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%2f53234306%2fpostgres-cursor-targets%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
up vote
0
down vote
I hope so documentation is clean.
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
So you cannot to use temporary table as target. Use record variable if there are not any special requirement:
DECLARE
r record;
mycursor refcursor;
BEGIN
OPEN mycursor ..
LOOP
FETCH mycursor INTO r;
EXIST WHEN NOT FOUND;
RAISE NOTICE 'personid: %', r.personid;
For this simple task is better to use FOR IN SELECT statement.
DECLARE r record;
BEGIN
FOR r IN SELECT p.personid, ...
LOOP
RAISE NOTICE 'personid: %', r.personid;
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
add a comment |
up vote
0
down vote
I hope so documentation is clean.
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
So you cannot to use temporary table as target. Use record variable if there are not any special requirement:
DECLARE
r record;
mycursor refcursor;
BEGIN
OPEN mycursor ..
LOOP
FETCH mycursor INTO r;
EXIST WHEN NOT FOUND;
RAISE NOTICE 'personid: %', r.personid;
For this simple task is better to use FOR IN SELECT statement.
DECLARE r record;
BEGIN
FOR r IN SELECT p.personid, ...
LOOP
RAISE NOTICE 'personid: %', r.personid;
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
add a comment |
up vote
0
down vote
up vote
0
down vote
I hope so documentation is clean.
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
So you cannot to use temporary table as target. Use record variable if there are not any special requirement:
DECLARE
r record;
mycursor refcursor;
BEGIN
OPEN mycursor ..
LOOP
FETCH mycursor INTO r;
EXIST WHEN NOT FOUND;
RAISE NOTICE 'personid: %', r.personid;
For this simple task is better to use FOR IN SELECT statement.
DECLARE r record;
BEGIN
FOR r IN SELECT p.personid, ...
LOOP
RAISE NOTICE 'personid: %', r.personid;
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
I hope so documentation is clean.
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not.
So you cannot to use temporary table as target. Use record variable if there are not any special requirement:
DECLARE
r record;
mycursor refcursor;
BEGIN
OPEN mycursor ..
LOOP
FETCH mycursor INTO r;
EXIST WHEN NOT FOUND;
RAISE NOTICE 'personid: %', r.personid;
For this simple task is better to use FOR IN SELECT statement.
DECLARE r record;
BEGIN
FOR r IN SELECT p.personid, ...
LOOP
RAISE NOTICE 'personid: %', r.personid;
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
answered Nov 11 at 18:40
Pavel Stehule
22.2k34856
22.2k34856
add a comment |
add a comment |
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.
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%2f53234306%2fpostgres-cursor-targets%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
