Can I return a list of strings from a Postgres function?

Question

I have a materialized view in Postgres like

CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table WHERE my_column NOT IN ('a', 'b', 'c');

But ('a', 'b', 'c') is part of my business logic, and I want to be able to make it into ('a', 'b', 'c', 'd') or ('a') without having to drop my_view and recreate it, since it has a bunch of dependent views. So I want to replace the definition of my_view to something like

CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table WHERE my_column NOT IN my_unwanted_list();

And then my_unwanted_list would just return a constant that I could set to whatever I want by updating my_unwanted_list's definition. Then I can add an unwanted value just by updating my_unwanted_list and refreshing my_view.

But I am having some trouble getting the syntax right for my_unwanted_list. How do I write a PostgreSql function that returns something I can use in an IN filter?

Nick Barnes · Accepted Answer · 2016-05-24 02:12:32Z

1

You can put the values in an array, and compare against its elements using the ANY() / ALL() constructs:

CREATE FUNCTION my_unwanted_list() RETURNS TEXT[] AS $$
  SELECT ARRAY['a', 'b', 'c']
$$ LANGUAGE sql IMMUTABLE;

CREATE MATERIALIZED VIEW my_view AS
  SELECT * FROM my_table
  WHERE my_column <> ALL(my_unwanted_list());

answered May 24, 2016 at 2:12

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

Comments

Ezequiel Tolnay · Accepted Answer · 2016-05-24 05:32:08Z

1

If you want your function to return the results of a query, or you rather use IN instead of <>ALL(array) (as @NickBarnes suggests), you can do the following:

CREATE FUNCTION my_unwanted_list() RETURNS SETOF TEXT AS $$
  SELECT col
  FROM table
  WHERE condition
$$ LANGUAGE sql IMMUTABLE;

CREATE MATERIALIZED VIEW my_view AS
  SELECT * FROM my_table
  WHERE my_column NOT IN (SELECT my_unwanted_list());

answered May 24, 2016 at 5:32

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Collectives™ on Stack Overflow

Can I return a list of strings from a Postgres function?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related