Use schema search path in views





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















I am experimenting a little with PostgreSQL schemas and found that the search path doesn't seem to be used in views. I have two schemas, one called bf and public. My search path is set as bf,public. Both schemas have the same tables, the table in public is empty.



When using this view:



CREATE VIEW public.testview(data1, data2)
AS
SELECT data1, data2 FROM the_table;


always only data from public (i.e. nothing) is shown.



I expect the data from bf, because bf.the_table is found first, according to the search path.



Update the view seems to automatically include the tablename including the schema name. When creating and omitting the schema name, it shows after changing the schema search path.










share|improve this question




















  • 1





    Maybe what you want can be done with rules. (AFAIK a view is translated to a rule too, so you might want to start with that rule and try to modify it to use a variable schema.)

    – sticky bit
    Jan 3 at 13:28











  • @stickybit I looked at the documentation, but haven't found a way to implement what I want, could you perhaps elaborate?

    – Bart Friederichs
    Jan 3 at 14:27


















0















I am experimenting a little with PostgreSQL schemas and found that the search path doesn't seem to be used in views. I have two schemas, one called bf and public. My search path is set as bf,public. Both schemas have the same tables, the table in public is empty.



When using this view:



CREATE VIEW public.testview(data1, data2)
AS
SELECT data1, data2 FROM the_table;


always only data from public (i.e. nothing) is shown.



I expect the data from bf, because bf.the_table is found first, according to the search path.



Update the view seems to automatically include the tablename including the schema name. When creating and omitting the schema name, it shows after changing the schema search path.










share|improve this question




















  • 1





    Maybe what you want can be done with rules. (AFAIK a view is translated to a rule too, so you might want to start with that rule and try to modify it to use a variable schema.)

    – sticky bit
    Jan 3 at 13:28











  • @stickybit I looked at the documentation, but haven't found a way to implement what I want, could you perhaps elaborate?

    – Bart Friederichs
    Jan 3 at 14:27














0












0








0








I am experimenting a little with PostgreSQL schemas and found that the search path doesn't seem to be used in views. I have two schemas, one called bf and public. My search path is set as bf,public. Both schemas have the same tables, the table in public is empty.



When using this view:



CREATE VIEW public.testview(data1, data2)
AS
SELECT data1, data2 FROM the_table;


always only data from public (i.e. nothing) is shown.



I expect the data from bf, because bf.the_table is found first, according to the search path.



Update the view seems to automatically include the tablename including the schema name. When creating and omitting the schema name, it shows after changing the schema search path.










share|improve this question
















I am experimenting a little with PostgreSQL schemas and found that the search path doesn't seem to be used in views. I have two schemas, one called bf and public. My search path is set as bf,public. Both schemas have the same tables, the table in public is empty.



When using this view:



CREATE VIEW public.testview(data1, data2)
AS
SELECT data1, data2 FROM the_table;


always only data from public (i.e. nothing) is shown.



I expect the data from bf, because bf.the_table is found first, according to the search path.



Update the view seems to automatically include the tablename including the schema name. When creating and omitting the schema name, it shows after changing the schema search path.







postgresql schema






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 13:22







Bart Friederichs

















asked Jan 3 at 12:59









Bart FriederichsBart Friederichs

25.1k1168124




25.1k1168124








  • 1





    Maybe what you want can be done with rules. (AFAIK a view is translated to a rule too, so you might want to start with that rule and try to modify it to use a variable schema.)

    – sticky bit
    Jan 3 at 13:28











  • @stickybit I looked at the documentation, but haven't found a way to implement what I want, could you perhaps elaborate?

    – Bart Friederichs
    Jan 3 at 14:27














  • 1





    Maybe what you want can be done with rules. (AFAIK a view is translated to a rule too, so you might want to start with that rule and try to modify it to use a variable schema.)

    – sticky bit
    Jan 3 at 13:28











  • @stickybit I looked at the documentation, but haven't found a way to implement what I want, could you perhaps elaborate?

    – Bart Friederichs
    Jan 3 at 14:27








1




1





Maybe what you want can be done with rules. (AFAIK a view is translated to a rule too, so you might want to start with that rule and try to modify it to use a variable schema.)

– sticky bit
Jan 3 at 13:28





Maybe what you want can be done with rules. (AFAIK a view is translated to a rule too, so you might want to start with that rule and try to modify it to use a variable schema.)

– sticky bit
Jan 3 at 13:28













@stickybit I looked at the documentation, but haven't found a way to implement what I want, could you perhaps elaborate?

– Bart Friederichs
Jan 3 at 14:27





@stickybit I looked at the documentation, but haven't found a way to implement what I want, could you perhaps elaborate?

– Bart Friederichs
Jan 3 at 14:27












0






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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022807%2fuse-schema-search-path-in-views%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






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.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022807%2fuse-schema-search-path-in-views%23new-answer', 'question_page');
}
);

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







Popular posts from this blog

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

SQL update select statement

'app-layout' is not a known element: how to share Component with different Modules