Postgresql table function: get where condition 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;
The temporary table will only make things slower, you don't need it. And if you change the function to be a SQL function, the Postgres optimizer can push the condition from the WHERE clause automatically into the query inside the function:

create or replace function public.fn_world_cities()
  returns table (city_id int, city_name varchar, country_id int)
as $$
  select city_id, city_name, country_id
  from public.table_world_cities
  where row_status = 'A';
$$ 
language sql 
STABLE; --&lt;&lt; this is important!


If you now run:

select * 
from public.fn_world_cities() 
where country_id = 10;


And country_id has an index that is worth using, Postgres will use it Note that you need a non-trivial number of rows in that table, and the condition needs to be selective enough to warrant an index scan (as with any other index usage)