Generate HTML from PostgreSQL function with sql sentence for input

Question

can anybody tell me, how to write function in plpgsql, which generates html table and is called like this:

SELECT funktionName( 'Select name, salary*1.2 from employees where salary IN ( SELECT MIN(SALARY) from employees where salary>'||salary||') ' ) from employees where ssn=111;

Thanks for help.

+1 and @whoever-downvoted this is not a bad question so why vote it down? — user533832
– user533832, Commented Dec 13, 2010 at 18:37

user330315 · Accepted Answer · 2010-11-30 13:57:58Z

1

You can generate XML using Postgres' XML features and transform that into HTML using XSLT. There is an example for that in the manual

But this is definitely not a recommended approach.

answered Nov 30, 2010 at 13:57

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

ObiWanKenobi Over a year ago

"But this is definitely not a recommended approach." -- Why not?

Chris Travers Over a year ago

@ObiWanKenobi What I would worry about re: generating html in the db is that as presentation needs change, you end up having to rewrite all your stored procedures. It also reduces re-use of code. I am not saying there isn't a time or place for it. For example being able to take arbitrary DB objects and create a semantic XHTML document out of them might be relatively useful. However, I would be relatively careful and use this approach sparingly.

ObiWanKenobi Over a year ago

@ChrisTravers: "as presentation needs change, you end up having to rewrite all your stored procedures" -- Actually, no, since the original poster's suggestion was to generate XML, and then apply XSLT to that to create the HTML, which means that as presentation needs change, you don't change your stored procedure at all, just your XSLT...

Chris Travers Over a year ago

Depends. Are you doing XSLT in your stored procedure? I mean you could do that using one of the PLs and output xhtml, right?

Chris Travers · Accepted Answer · 2012-10-05 02:03:24Z

0

Another interesting approach would be to download and install http://www.commandprompt.com/community/plphp/

You could then use PHP to write templates for the XML that would be executed in the db.

answered Oct 5, 2012 at 2:03

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

Generate HTML from PostgreSQL function with sql sentence for input

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related