PostgreSQL: Use parameter of function in where clause

Question

I want to create a function that uses a filter-parameter, that is later applied to a where-clause.

The definition of my function so far looks like:

CREATE OR REPLACE FUNCTION function_to_be_fixed(filter_date date)                   
RETURNS void as  $$
Begin
  CREATE OR REPLACE VIEW view_to_create.randomname AS
  SELECT * from other_table where date_col <= filter_date
End;
$$ LANGUAGE plpgsql;

When calling the function via

 select function_to_be_fixed(filter_date   => '2020-01-01');

I receive the error: column 'filter_date' does not exist.

What do I need to adjust in order to make it run?

As I have commented on your other question as well: Why do you want to create thousands of views? Providing a set returning function that returns the desired sub-set of the table looks like a much easier way to do this. — user330315
– user330315, Commented Jul 29, 2019 at 13:58
@a_horse_with_no_name: I need to pull data from the database using python. The request contains a filterdate, that is variable. Is there another nice way to do this without a function? — Jogi
– Jogi, Commented Jul 30, 2019 at 5:28
What's wrong with a function? Surely python can call functions in a SQL statement? select * from get_data('2020-01-01'); — user330315
– user330315, Commented Jul 30, 2019 at 5:37

score 2 · Accepted Answer · 2019-07-30 05:49:46Z

2

You can't use a parameter like that inside the view definition.

Once the view is created the parameter would be "lost".

You need to use dynamic SQL for that, so that the value of the parameter is replace into the SQL string that defines the view:

CREATE OR REPLACE FUNCTION function_to_be_fixed(filter_date date)                   
RETURNS void as  $$
Begin
  execute format('
    CREATE OR REPLACE VIEW view_to_create.randomname AS
    SELECT * from other_table where date_col <= %L', filter_date);
End;
$$ LANGUAGE plpgsql;

A single function that you pass the filter_date to would a better solution:

create or replace function get_some_table_data(filter_date date)
  returns setof some_table
as
$$
  select *
  from some_table
  where date_col <= filter_date;
$$
language sql;

edited Jul 30, 2019 at 5:49

answered Jul 29, 2019 at 13:56

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Jogi Over a year ago

Many thanks for the comprehensive reply! I just tested your suggestion for my purposes - having two tables - and recalled that one needs to slightly adapt the exaple. Correct me if I'm wrong, but one then has to state the definition of each column: e.g. RETURNS table (col1 as integer, col2 integer) as $$

Collectives™ on Stack Overflow

PostgreSQL: Use parameter of function in where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related