Thursday, 16 October 2014

Return xml from stored procedure(postgres)

See below example.

drop function getXml5(IN service_version varchar);
CREATE OR REPLACE FUNCTION getXml5(IN service_version varchar)
  RETURNS xml 
  AS
$BODY$
DECLARE myXml xml;
BEGIN 
    WITH services AS (
        SELECT servicename, serviceversion FROM audit.SECURITY WHERE serviceversion= service_version
    ) SELECT XMLELEMENT(NAME services,
        XMLAGG(XMLELEMENT(NAME service,
        XMLATTRIBUTES(
            servicename AS name,
            serviceversion AS version
        ))))
        into myXml
    FROM services;

RETURN myXml;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getXml5(IN service_version varchar) OWNER TO postgres;

SELECT getXml5('0.0.1'); -- get xml 
SELECT getXml5('0.0.2'); -- get "<services/>" as no service 0.0.2


Apache Velocity - Automatic email

Apache Velocity is a Java-based template engine that provides a template language to reference objects defined in Java code. It aims to ensure clean separation between the presentation tier and business tiers in a Web application (the model–view–controller design pattern).
Velocity is an open source software project hosted by the Apache Software Foundation. It is released under the Apache License.

Uses[edit]

Some common types of applications that use Velocity are:
  • Web applicationsWeb designers create HTML pages with placeholders for dynamic information. The page is processed with VelocityViewServlet or any of a number of frameworks which support Velocity.
  • Source code generation: Velocity can be used to generate Java source code, SQL, or PostScript, based on templates. A number of open source and commercial development software packages use Velocity in this manner.[1]
  • Automatic emails: Many applications generate automatic emails for account signup, password reminders, or automatically sent reports. Using Velocity, the email template can be stored in a text file, rather than directly embedded in Java code.
  • XML transformation: Velocity provides an Ant task, called Anakia, which reads an XML file and makes it available to a Velocity template. A common application is to convert documentation stored in a generic "xdoc" format into a styled HTML document.

Code example[edit]

The following template:
## Velocity Hello World
<html>
    <body>
       #set( $foo = "Velocity" )
       ## followed by
       Hello $foo World!
    </body>
</html>
processed by Velocity will produce the following HTML:
<html>
    <body>
     Hello Velocity World!
    </body>
</html>
The syntax and overall concept of the Apache Velocity templates is very similar to the syntax of the older WebMacro template engine which is now also an open source project.[citation needed]

More than be found here.


CTE (Common table expression)

Common Table Expression(CTE) is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.It computes the aggregation once, and allows us to reference it by its name (may be multiple times) in the queries.It materialize subqueries thereby helping oracle not to recompute them multiple times.In PostgreSQL, we use the WITH clause for writing CTE based queries.It helps in breaking down complicated and large queries into simpler forms which is easily readable.
Let us see how we can do so
Simple CTE
01.With CTE AS
02.(  
03.Select
04.empid
05., empname
06., salary
07., belongsto
08., deptid
09.FROM tblemployee
10. 
11.)
12.Select From CTE;
One thing to notice here is that in Sql Server we start with a semicolon(;) before writing a CTE inorder to differentiate it from other statements previously written.
This is a simple CTE where basically we are projecting the employee table records
Multiple CTE or Chain CTE
01.With CTEEmp AS
02.(  
03.Select
04.empid
05., empname
06., salary
07., belongsto
08., deptid
09.FROM tblemployee
10.)
11.,CTEDept As
12.(
13.Select e.*,d.deptname
14.From CTEEmp e
15.Join tbldept d
16.On e.deptid = d.deptid
17.)
18.Select From CTEDept;
It is call as CTE chains/multiple CTEs.We can find that we are using the resultset of the first CTE (CTEEmp) into the second one(CTEDept) to obtain the result
Recursive CTE or Hierarchial queries
It is the third form of CTE where a CTE can reference to itself. So we call it as recursive CTE. In PostgreSQL, we can achieve so by using the Recursive keyword in the CTE which ensures that the query can refer to its own output
In the next example we will look into how we can generate a number table by the help of Recursive CTE
1.With Recursive CTE(Rn) AS
2.(  
3.Select 1
4.Union All
5.Select Rn + 1 From CTE
6.Where Rn < 10
7.)
8.Select From CTE;
In Sql Server , CTE was introduce since version 2005. It is available in all the three flavours described here. But for using a recursive CTE, we donot use "Recursive" keyword. We will write the same program for generating a number table using recursive CTE using SQL Serevr CTE syntax below
1.;With CTE AS
2.(  
3.Select Rn = 1
4.Union All
5.Select Rn + 1 From CTE
6.Where Rn < 10
7.)
8.Select From CTE;

link

====================================================================
SQL by default isn’t typically friendly to dive into, and especially so if you’re reading someone else’s already created queries. For some reason most people throw out principles we follow in other languagessuch as commenting and composability just for SQL. I was recently reminded of a key feature in Postgres that most don’t use by @timonk highlighting it in his AWS Re:Invent Redshift talk. The simple feature actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be.
The feature itself is known as CTEs or common table expressions, you may also here it referred to asWITH clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you’re doing.
Lets take a look at a nice simple one:
WITH users_tasks AS (
  SELECT 
         users.email,
         array_agg(tasks.name) as task_list,
         projects.title
  FROM
       users,
       tasks,
       project
  WHERE
        users.id = tasks.user_id
        projects.title = tasks.project_id
  GROUP BY
           users.email,
           projects.title
)
Using this I could now just append some basic other query on to the end that references this CTEusers_tasks. Something akin to:
SELECT *
FROM users_tasks;
But where it becomes more interesting is chaining these together. So while I have all tasks assigned to each user here, perhaps I want to then find which users are responsible for more than 50% of the tasks on a given project, thus being the bottleneck. To oversimplify this we could do it a couple of ways, total up the tasks for each project, and then total up the tasks for each user per project:
total_tasks_per_project AS (
  SELECT 
         project_id,
         count(*) as task_count
  FROM tasks
  GROUP BY project_id
),

tasks_per_project_per_user AS (
  SELECT 
         user_id,
         project_id,
         count(*) as task_count
  FROM tasks
  GROUP BY user_id, project_id
),
Then we would want to combine and find the users that are now over that 50%:
overloaded_users AS (
  SELECT tasks_per_project_per_user.user_id,

  FROM tasks_per_project_per_user,
       total_tasks_per_project
  WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
)
Now as a final goal I’d want to get a comma separated list of tasks of the overloaded users. So we’re simply giong to join against that overloaded_users and our initial list of users_tasks. Putting it all together it looks somewhat long, but becomes much more readable. And as a bonus I layered in some comments.
--- Created by Craig Kerstiens 11/18/2013
--- Query highlights users that have over 50% of tasks on a given project
--- Gives comma separated list of their tasks and the project


--- Initial query to grab project title and tasks per user
WITH users_tasks AS (
  SELECT 
         users.id as user_id,
         users.email,
         array_agg(tasks.name) as task_list,
         projects.title
  FROM
       users,
       tasks,
       project
  WHERE
        users.id = tasks.user_id
        projects.title = tasks.project_id
  GROUP BY
           users.email,
           projects.title
),

--- Calculates the total tasks per each project
total_tasks_per_project AS (
  SELECT 
         project_id,
         count(*) as task_count
  FROM tasks
  GROUP BY project_id
),

--- Calculates the projects per each user
tasks_per_project_per_user AS (
  SELECT 
         user_id,
         project_id,
         count(*) as task_count
  FROM tasks
  GROUP BY user_id, project_id
),

--- Gets user ids that have over 50% of tasks assigned
overloaded_users AS (
  SELECT tasks_per_project_per_user.user_id,

  FROM tasks_per_project_per_user,
       total_tasks_per_project
  WHERE tasks_per_project_per_user.task_count > (total_tasks_per_project / 2)
)

SELECT 
       email,
       task_list,
       title
FROM 
     users_tasks,
     overloaded_users
WHERE
      users_tasks.user_id = overloaded_users.user_id
CTEs won’t always be quite as performant as optimizing your SQL to be as concise as possible. In most cases I have seen performance differences smaller than a 2X difference, this tradeoff for readability is a nobrainer as far as I’m concerned. And with time the Postgres optimizer should continue to get better about such performance.
As for the verbosity, yes I could have done this query in probably 10-15 lines of very concise SQL. Yet, most may not be able to understand it quickly if at all. Readability is huge when it comes to SQL to ensure its doing the right thing. SQL will almost always tell you an answer, it just may not be to the question you think you’re asking. Ensuring your queries can be reasoned about is critical to ensuring accuracy and CTEs are one great way of accomplishing that.
wiki page
===================================================================

WITH services AS (
    SELECT servicename, serviceversion FROM audit.SECURITY WHERE serviceversion='0.0.1'
) SELECT XMLELEMENT(NAME services,
    XMLAGG(XMLELEMENT(NAME service,
    XMLATTRIBUTES(
            servicename AS name,
            serviceversion AS version
    ))))
FROM services;

SELECT
    XMLFOREST(tb1."xml-val" AS "services")
    FROM
    (
    SELECT
        XMLAGG(XMLELEMENT(NAME service,
                XMLATTRIBUTES(
                    servicename AS name,
                    serviceversion AS version)
                )
                 )
            "xml-val"
        FROM
            audit.SECURITY t
) AS tb1;


SELECT
    XMLFOREST("xml-val" AS "services")
    FROM
    (
    SELECT
        XMLAGG(XMLELEMENT(NAME service,
                XMLATTRIBUTES(
                    servicename AS name,
                    serviceversion AS version)
                )
                 )
        FROM
            audit.SECURITY t
) AS tb1;


CREATE OR REPLACE FUNCTION getXml3()
  RETURNS xml 
  AS
$BODY$
DECLARE myXml xml;
BEGIN 

SELECT
    XMLFOREST(tb1."xml-val" AS "services")
    into myXml
    FROM
    (
    SELECT
        XMLAGG(XMLELEMENT(NAME service,
                XMLATTRIBUTES(
                    servicename AS name,
                    serviceversion AS version)
                )
                 )
            "xml-val"
        FROM
            audit.SECURITY t
) AS tb1;

  
    RETURN myXml;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getXml3() OWNER TO postgres;

SELECT getXml3();

--------------------


CREATE OR REPLACE FUNCTION getXml4()
  RETURNS xml 
  AS
$BODY$
DECLARE myXml xml;
BEGIN 
    WITH services AS (
        SELECT servicename, serviceversion FROM audit.SECURITY WHERE serviceversion='0.0.1'
    ) SELECT XMLELEMENT(NAME services,
        XMLAGG(XMLELEMENT(NAME service,
        XMLATTRIBUTES(
            servicename AS name,
            serviceversion AS version
        ))))
        into myXml
    FROM services;

RETURN myXml;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getXml4() OWNER TO postgres;

SELECT getXml4();
--------------


---------------------------------
With CTE(servicename, serviceversion) AS
(    
  Select 
    servicename
    , serviceversion

  FROM audit.SECURITY where serviceversion = '0.0.1'
)

------------
--chained
With CTE AS
(    
  Select 
    'Initial upload patients not required' AS description,
    servicename
    , serviceversion

  FROM audit.SECURITY where serviceversion = '0.0.1'
),
CTE2 as (
select count(*) from CTE 
)
Select * from CTE2



---------------


Total visitors since Jan 2012

World Visitor Map