Postgresql table function: get where condition
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.
Here, there is a very simple example:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A'; ---- filter active record
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
The temporary table creation is time expensive.
When I use the function, always use some WHERE condition. Like:
select * from public.fn_world_cities()
where country_id = 10;
In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone.
Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation.
I wish I can have a function like this:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A' ---- filter active record
and ***WHERE CONDITION APPLIED TO FUNCTION CALL***
;
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
postgresql postgresql-performance functions set-returning-functions
asked 31 mins ago
RadioleaoRadioleao
62
add a comment |
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.
Here, there is a very simple example:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A'; ---- filter active record
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
The temporary table creation is time expensive.
When I use the function, always use some WHERE condition. Like:
select * from public.fn_world_cities()
where country_id = 10;
In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone.
Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation.
I wish I can have a function like this:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A' ---- filter active record
and ***WHERE CONDITION APPLIED TO FUNCTION CALL***
;
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
postgresql postgresql-performance functions set-returning-functions
asked 31 mins ago
RadioleaoRadioleao
62
add a comment |
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.
Here, there is a very simple example:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A'; ---- filter active record
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
The temporary table creation is time expensive.
When I use the function, always use some WHERE condition. Like:
select * from public.fn_world_cities()
where country_id = 10;
In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone.
Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation.
I wish I can have a function like this:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A' ---- filter active record
and ***WHERE CONDITION APPLIED TO FUNCTION CALL***
;
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
postgresql postgresql-performance functions set-returning-functions
asked 31 mins ago
RadioleaoRadioleao
62
I have defined a function that returns a table. In this function, I use multiple temporary table to pre-elaborate data.
Here, there is a very simple example:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A'; ---- filter active record
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
The temporary table creation is time expensive.
When I use the function, always use some WHERE condition. Like:
select * from public.fn_world_cities()
where country_id = 10;
In my function, there are many fields on which I can set a WHERE, so it's impossible to add function parameters for everyone.
Is there a way, at run time, to know there WHERE condition values? In that way I can pre-filter data during temporary table creation.
I wish I can have a function like this:
create or replace function public.fn_world_cities()
returns table (
city_id int,
city_name varchar,
country_id int
)
as $$
begin
---- Extract temp table with all cities
create temporary table tmp_all_cities on commit drop
as
begin $$
select city_id, city_name, country_id
from public.table_world_cities
where row_status = 'A' ---- filter active record
and ***WHERE CONDITION APPLIED TO FUNCTION CALL***
;
---- Return data
return query
select city_id, city_name, country_id
from tmp_all_cities;
end; $$ language plpgsql;
postgresql postgresql-performance functions set-returning-functions
postgresql postgresql-performance functions set-returning-functions
asked 31 mins ago
RadioleaoRadioleao
62
asked 31 mins ago
RadioleaoRadioleao
62
asked 31 mins ago
RadioleaoRadioleao
62
asked 31 mins ago
RadioleaoRadioleao
62
asked 31 mins ago
RadioleaoRadioleao
62
62
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f234326%2fpostgresql-table-function-get-where-condition%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
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f234326%2fpostgresql-table-function-get-where-condition%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
