How to use variable in view definition in Postgres/plpgsql

Question

I am using plpgsql with Postgres 10.6. I have a function that declares and gives value to a variable. That function also defines a view, and I would like to use the variable within the definition.

  create view myview as
    select
      some_columns
    from
      mytable
    where      
      id = _id     /*_id is declared earlier in function */
  ;

In this case, the function can be defined, but when it is run it gives an error: UndefinedColumn: column "_id" does not exist

Is such a thing possible in Postgres? Can views include variables as part of their definition?

I do see here that in BigQuery (which I have never used), what I am asking is not possible, which makes me think it may also not be possible in plpgsql.

It is not a big deal, but I am curious. A workaround - and probably the recommended solution - is to pass the _id when I select from the view (e.g. select * from myview where id = 3). Or, if I really want to keep the select call simple (which I do, because my actual implementation is more complicated and has multiple variables), I could define the view as a string and use execute within the function (this is all internal stuff used in building up and creating a db, not in a situation where the various risks inherent to dynamic sql are a concern).

user330315 · Accepted Answer · 2020-08-11 04:50:42Z

7

No, you can not pass a variable to a view. But you can do this with a function:

create function my_view_function(p_id integer)
  returns table (id int, column_1 int, column2 text)
as
$$
  select id, column_1, column_2
  from my_table
  where id = p_id;
$$
language sql
stable;

Then use it like this

select *
from my_view_function(42);

answered Aug 11, 2020 at 4:50

user330315

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

1 Comment

susie derkins Over a year ago

Thanks for the definitive "no, you cannot pass a variable to a view" answer. Also, your solution is a good solution to accomplish what I am trying to do. Thank you.

Collectives™ on Stack Overflow

How to use variable in view definition in Postgres/plpgsql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related