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