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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general