Sample Header Ad - 728x90

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