DEV Community

Discussion on: Explain Postgresql Views Like I'm Five

Collapse
4lch4 profile image
Devin W. Leaman

If I'm not mistaken then, they sound like assigning a method name to an SQL function that was built previously.

Am I close?

Collapse
vinibrsl profile image
Vinicius Brasil • Edited on

Yes, you're getting close. I'll try to write some pseudo code so you can understand better:

// creating a view
paid_orders_view = SELECT * FROM orders WHERE status = 'PAID';

// using this view
SELECT SUM(subtotal) FROM paid_orders_view;

Not assigning a function, but a SQL statement. In most cases a query.

Thread Thread
ben profile image
Ben Halpern

Why not have this logic stored in the program itself, even as a simple string variable? What am I gaining from using this functionality?

Not challenging, 100% curious.

Thread Thread
isaacdlyman profile image
Isaac Lyman

One nice thing about a function stored in SQL is that SQL can create an "execution plan" and store it as well, so it doesn't have to reparse and plan everything each time you submit the query. It speeds things up, especially with queries you use frequently.

Thread Thread
erikpischel profile image
Erik Pischel

For me, views often are kind of abstractions or interfaces. Especially when the view contains joins. It hides the details of the data model. Database programmers can even change those details while maintaining the SELECT clause of the view so for the application programmer the interface has not changed.

Thread Thread
vinibrsl profile image
Vinicius Brasil • Edited on

Hey Ben,

SQL views aren't just stored queries, it's a virtual table. You can make joins and select specific columns, granting permissions to different database users to it, instead of granting permission to every table. Views are also indexed, when talking about materialized views.

So by using SQL views you've got: security, performance and simplicity. Also, not every system is backed by a modern application layer.