Postgresql table function: get where condition
0
votes
1
answer
145
views
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;
Asked by Radioleao
(153 rep)
Apr 9, 2019, 03:26 PM
Last activity: Jul 24, 2025, 11:06 PM
Last activity: Jul 24, 2025, 11:06 PM