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;
0

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; --<< 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)

  • you mean, I have to create a function which contains only a single query? I've tried, but a single query is much slower. With temporary table I can create index to speed up next query. – Radioleao Apr 12 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.