CODEWITHSUNDEEP
postgresqlfunctionviewparameters
Jogi
Jogi

Reputation: 314

PostgreSQL: Use parameter of function in where clause

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?

Upvotes: 0

Views: 3947

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions