I'm looking at moving to PostgreSQL from MySQL for both my personal projects and a few small projects at work. I'm not trying to start a "which database is better and why" discussion, so I'll move on quickly from that point. :-) Now, one of the big things Pg proponents have said all along is that it supports views, stored procedures, triggers, and the like. I, probably like many here, am completely self-taught with respect to database theory. I can design a normalized table structure, but I've never played around with any of the above.

So, of course, I go looking for explanations of both how and, more importantly, why/when to use these "advanced" features. It seems, like in many things, there is more than one way to do it. Most of the examples fall into two categories:

• Simplifying complex queries: views for static but complicated joins, stored procedures otherwise
• Presenting a unified front, either for a redesign of the table structure or for hiding the table structure from applications (a quick overview of this is at Applying Advanced Database Features, found via this node)

My concern is that both of these are mostly (or entirely) unnecessary if one is using a dynamic SQL generator like Class::DBI. Google pointed me to this weblog post refuting a separate article promoting the use of stored procedures. In particular:

"I'm now almost stored procedure 'free' for 8 months now, and I love the feeling. The reason is obvious: the stress of maintaining a lot of stored procedures, to write another stored procedure for each thing you want to do, is gone. Dynamic SQL is the future. (Dynamic SQL is generated on the fly by a generic piece of code which gets various data as input and generates a parametrized query from it.)"

I'm curious. Do you use database features like the above if you've got something as powerful as CDBI running your application? Would there be a situation where they would be useful? It seems that, unless you were just using views that "looked" like regular tables, you would have to use set_sql or some such to take advantage of any of the above. I'm not against that by any means, but it almost seems like CDBI wasn't really planning on its users using a lot of database-side processing like this. Thoughts?

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.