Thread: Application written in pure pgsql, good idea?
Is it a good idea to write a simple application (consisting of just data entry interfaces) in pure pgsql?
Basically, we would have each page has a stored function in postgresql that is called by php+apache (the http get/post values would be passed into postgrel as an array).
The pgpsql would render HTML and return back to the front end for display.
Sample:
create function render_user_login_page(out v_html varchar)
returns varchar
as
$$
$$
begin
v_html := v_html || '<table><tr><td>User ID:</td><td><input type="text" /></td></tr></table>';
end;
$$
$$
Would there be any performance issues with doing this?
On February 28, 2015 03:39:06 PM inspector morse wrote: > Is it a good idea to write a simple application (consisting of just data > entry interfaces) in pure pgsql? > > Basically, we would have each page has a stored function in postgresql that > is called by php+apache (the http get/post values would be passed into > postgrel as an array). > > The pgpsql would render HTML and return back to the front end for display. > > Sample: > create function render_user_login_page(out v_html varchar) > returns varchar > as > $$ > begin > v_html := v_html || '<table><tr><td>User ID:</td><td><input > type="text" /></td></tr></table>'; > end; > $$ > > > Would there be any performance issues with doing this? Don't know about the performance aspects, but just thinking about it you're making your db server responsible for a lot of cruft that can easily be outsourced - the HTML rendering. Which, besides being a potential performance pitfall, will probably end up being a terrible maintenance nightmare. What's the problem with letting PHP do what it's good at, i.e. rendering templatized HTML, and let the DB do what it's good at - data processing? The idea of sending stuff over straight to the DB sounds sane, but instead of doing that terrible string concat stuff you're thinking of just send back some structured data which you then render in PHP?
On 02/28/2015 01:39 PM, Jan de Visser wrote: > On February 28, 2015 03:39:06 PM inspector morse wrote: >> Is it a good idea to write a simple application (consisting of just data >> entry interfaces) in pure pgsql? >> >> Basically, we would have each page has a stored function in postgresql that >> is called by php+apache (the http get/post values would be passed into >> postgrel as an array). >> >> The pgpsql would render HTML and return back to the front end for display. >> >> Sample: >> create function render_user_login_page(out v_html varchar) >> returns varchar >> as >> $$ >> begin >> v_html := v_html || '<table><tr><td>User ID:</td><td><input >> type="text" /></td></tr></table>'; >> end; >> $$ >> >> >> Would there be any performance issues with doing this? > > Don't know about the performance aspects, but just thinking about it you're > making your db server responsible for a lot of cruft that can easily be > outsourced - the HTML rendering. Which, besides being a potential performance > pitfall, will probably end up being a terrible maintenance nightmare. The thought of doing CREATE OR REPLACE FUNCTION every time a Web page needed to be modified gives me the jitters. > > What's the problem with letting PHP do what it's good at, i.e. rendering > templatized HTML, and let the DB do what it's good at - data processing? The > idea of sending stuff over straight to the DB sounds sane, but instead of doing > that terrible string concat stuff you're thinking of just send back some > structured data which you then render in PHP? Yea, I am pretty sure that was the problem REST was created to solve. > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, Feb 28, 2015 at 3:39 PM, inspector morse <inspectormorse86@gmail.com> wrote:
Is it a good idea to write a simple application (consisting of just data entry interfaces) in pure pgsql?Basically, we would have each page has a stored function in postgresql that is called by php+apache (the http get/post values would be passed into postgrel as an array).The pgpsql would render HTML and return back to the front end for display.
I'm going to go ahead and say this is a bad idea for one primary reason: there is no PL/pgSQL framework or library to help you with the development tasks you'll need to perform. For example, there's no web framework to help interpret incoming requests. There's no templating library to help render pages. There won't be many other programming libraries to help with more specific data processing tasks.
On the other hand, if all you want is a simple "RESTful" (by which I mean "leverages the HTTP standard") CRUD interface, there are tools that will actually auto-generate these kind of applications for your front end to consume.
Bottom line: this isn't what PL/pgSQL was designed for or how it's commonly used, and that's more likely to bite you than the benefits you're hoping for are to help. Unless this is purely an experiment for fun or research, go with a more trodden path for your sanity and those who follow you.
This is just for fun/research, I don't need a web framework because PHP is actually picking up the incoming requests and sending it to a pgsql stored function. The pgsql will concatenate all the html that is required for the page and send it back to PHP to write out to the response.
My main concern is, since there will be a lot of concatenation in pgsql to generate the HTML, would it affect performance?On Sat, Feb 28, 2015 at 11:08 PM, BladeOfLight16 <bladeoflight16@gmail.com> wrote:
On Sat, Feb 28, 2015 at 3:39 PM, inspector morse <inspectormorse86@gmail.com> wrote:Is it a good idea to write a simple application (consisting of just data entry interfaces) in pure pgsql?Basically, we would have each page has a stored function in postgresql that is called by php+apache (the http get/post values would be passed into postgrel as an array).The pgpsql would render HTML and return back to the front end for display.I'm going to go ahead and say this is a bad idea for one primary reason: there is no PL/pgSQL framework or library to help you with the development tasks you'll need to perform. For example, there's no web framework to help interpret incoming requests. There's no templating library to help render pages. There won't be many other programming libraries to help with more specific data processing tasks.On the other hand, if all you want is a simple "RESTful" (by which I mean "leverages the HTTP standard") CRUD interface, there are tools that will actually auto-generate these kind of applications for your front end to consume.Bottom line: this isn't what PL/pgSQL was designed for or how it's commonly used, and that's more likely to bite you than the benefits you're hoping for are to help. Unless this is purely an experiment for fun or research, go with a more trodden path for your sanity and those who follow you.
On March 1, 2015 09:45:24 AM inspector morse wrote: > This is just for fun/research, I don't need a web framework because PHP is > actually picking up the incoming requests and sending it to a pgsql stored > function. The pgsql will concatenate all the html that is required for the > page and send it back to PHP to write out to the response. > > My main concern is, since there will be a lot of concatenation in pgsql to > generate the HTML, would it affect performance? Again, performance is the least of your concerns. Building HTML from raw strings is, except in the smallest of toys, an exercise in frustration. And in those toys performance won't be an issue anyway. Save yourself a bunch of aggravation and let PHP do the HTML for you. It's good at it. pl/pgsql isn't.
inspector morse wrote > This is just for fun/research, I don't need a web framework because PHP is > actually picking up the incoming requests and sending it to a pgsql stored > function. The pgsql will concatenate all the html that is required for the > page and send it back to PHP to write out to the response. > > My main concern is, since there will be a lot of concatenation in pgsql to > generate the HTML, would it affect performance? Then do research. Implement a somewhat complete example of what you are thinking in one or more languages/architectures and then run some performance testing - and make some subjective usability determinations - to see how they compare to each other. David J. -- View this message in context: http://postgresql.nabble.com/Application-written-in-pure-pgsql-good-idea-tp5839889p5839933.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sat, 28 Feb 2015 15:39:06 -0500 inspector morse <inspectormorse86@gmail.com> wrote: > Is it a good idea to write a simple application (consisting of just data > entry interfaces) in pure pgsql? > > Basically, we would have each page has a stored function in postgresql that > is called by php+apache (the http get/post values would be passed into > postgrel as an array). I did something this way with an obscure HTTP library for Oracle PL/SQL. I think "nightmare" is an appropriate word to describe the outcome. -- Alberto Cabello Sánchez <alberto@unex.es>
On Sat, Feb 28, 2015 at 2:39 PM, inspector morse <inspectormorse86@gmail.com> wrote: > > Is it a good idea to write a simple application (consisting of just data > entry interfaces) in pure pgsql? > > Basically, we would have each page has a stored function in postgresql that > is called by php+apache (the http get/post values would be passed into > postgrel as an array). > > The pgpsql would render HTML and return back to the front end for display. > > Sample: > create function render_user_login_page(out v_html varchar) > returns varchar > as > $$ > begin > v_html := v_html || '<table><tr><td>User ID:</td><td><input type="text" > /></td></tr></table>'; > end; > $$ > > > Would there be any performance issues with doing this? I think this idea is pretty neat. Posgres string processing will likely not be performance competitive with some of the more popular string processing engines, but it should be fast enough to get the job done in any cases. Some general tips: *) format() function will often yield more readable code than excessive string concatentation via || or concat() *) consider serving static html (perhaps directly via web server vs database rendered) and rethink your strategy to have the database compose json which is post rendered in the browser. This is a very powerful strategy *) advise liberally using powerful javascript libraries (slickgrid, highcharts, google visualizations, etc) vs doing database based constructions of same merlin