CODEWITHSUNDEEP
postgresql
Perlos
Perlos

Reputation: 2118

How to call function within a view

I'd like call function within a view to resolve virtual column. Calculating depends on data in actual row and did not want to multiple times selecting a data. Is this correct?? And like bonus, is possible call function within a call another?

CREATE VIEW my_view AS SELECT c.column1,c.columns2,... my_function(c) FROM my_table c

CREATE VIEW my_view AS SELECT c.money, c.quantity,... my_ratio_function(c.money,c.quantity,select sum_all_pays(my_view)) FROM my_table c

note. I'm put here because when looked for, cannot find that. If you have any other ideas, put it. Second one command not sure if is correct.

Upvotes: 1

Views: 7132

Answers (1)

vyegorov
vyegorov

Reputation: 22895

It is fine to use functions in the view definition. The only constraint — you should always give an explicit name to the column that is actually a function call, otherwise PostgreSQL doesn't know how to present a view definition.

Still, you cannot reference the view from inside the view. Instead, you might create 2 views, then you can reference the inside one from the outside view. Another approach is to use the WITH construct, which I find very handy and do use a lot.

Please note, that view is just a server-stored SQL and functions will be called for each row each time you will be querying the view. To get some performance improvements you might want to define your functions either as IMMUTABLE or as STABLE.

And I do agree with Frank — go ahead and test your views.

Upvotes: 1

Related Questions