Recursive With Query in Postgres

Recursive with Queries in Postgres

Sometimes we might have a requirement for certain hierarchy relation in data.
This can be done with the help of Recursive WITH Queries(Recursive CTE) in Postgres.

Consider the example –
Location is stored in table location_hierarchy.Query output will have all the children of ‘India’ along with its depth relative to “India”. The result should look somewhat similar to this:

Sample Data:

location data

In such cases, Self JOIN query will fail since the depth here is arbitrary and may change in future.

For this, we use a special query called WITH RECURSIVE. It is also called as PostgreSQL hierarchical query since it is usually used to query on hierarchical data, like the one which we are discussing. The WITH RECURSIVE actually is an extension of WITH query which is referred to as Common Table Expressions(CTE) in PostgreSQL. WITH query can be seen as forming a temporary table(s) which has a scope for a single query or as a named sub-query.

Query :
SELECT child, 1 AS depth ---|Non
FROM locatio_hierarchy --|Recursive
WHERE parent = 'India' ---|Part


SELECT a.child, depth+1 ---|Recursive
FROM locatio_hierarchy a --|Part
JOIN children b ON(a.parent = b.child) ---|
SELECT * FROM children


Query Output

Prasad Kshirsagar




Groovy Language

  • Groovy can run on JVM
  • Groovy scripts can be executed using groovy jar file.
  • Annotations are supported in groovy.

Groovy installation.

  • Binary download link
  • The binary can be run from command prompt
  • Set GROOVY_HOME, GROOVY_HOME\bin variable as enviroment variable.
  • After installing and running in the command line you get the groovy shell(groovysh).
  • groovy> println ” hello helical”

Source code in groovy.

  • The extension for groovy source file is .groovy
  • We can write either script or we may also encapsulate the code as class definition inside this file.
  • By default groovy class is subclassed by java.lang.Object.
  • Java class and its objects can access groovy script class and object and vice versa.
  • Using groovyc we can compile the source code.
  • Plain Old Groovy Objects (POGO).

Intersting facts about groovy.

  • Groovy doesnot require ; at the end of statement.
  • Last expression of the method is returned by default. So we may not explicitly use return keyword.
  • Maps, list and regular expression readily available in groovy.
  • The following package are readily available. (groovy.lang.*, groovy.util.* , java.lang.*, j ava.util.*,*,*, java.math.BigInteger, java.math.BigDecimal)
  • Its classes and methods are by-default public
  • Automatic getter and setter is created for the fields in groovy class.
  • Unlike java == operator in groovy checks for contents.
  • The is() may be used to check if two variables/object refer the same.
  • The parameters inside a function are optional.
  • Gpath is expression language to evaluate nested structure.

Data types in Groovy.

    • The keyword def is used to define variable. We can also use specific type for variable declaration. Example int marks, String company. etc
    • Range data type is a Collection. Ranges consists of two values separated by two dots.
for (i in 0..9) {
  println ("Hello $i")

assert 'L'..'P' == ['L', 'M', 'N', 'O','P']

String & Gstring.

  • ‘Strings may be enclosed inside single quote ‘.
  • “Mehod() calls and ${variable} substitution inside a string is known as Gstring”.
  • ”’Tripple single quotes can be used for multi line string”’
  • “”” Multi line Gstring can be enclosed in tripple quotes”””
  • /regularexpression/

Methods in Groovy.

  • Groovy supports operator overloading. – operator can be used to substract string, << can be used for string concatination.
  • Groovy string has .toUrl() method that can be directly used to convert a string to Url encoding string.



package packageName
class ClassName {
  static void main(def args){
    def hetroList= [1,2,"hi","45"]
    hetroList.each{ println it }

I-Report Bands

                                                                                                        I-Report Bands


A jasper report is composed by a set of sections named as “bands”.  Every band contains few properties such as height. Band height should be greater than 0, otherwise it will never be visible to user.  The band height can grow if elements inside it are stretched.

Default Bands in Report:

Title Band

Printedonly one time and it’s the first band. It can be printed on a separate page. You can print report title in this band.


Page Header

Printed on each page.


Column Header

It prints on each page if page contains detail band. If page splits it prints on each page. You can use this to crate manual table and put column name in this band.



It prints for each record in the source.


Column Footer

It is same as column header band, but it prints in footer instead of header.


Page Footer

Same as Page header, but it is footer section.



It prints only once at the end of the report. It can be printed on separate page also.



It can be used to define a page background. You can also use image in this band.


The position of bands cannot be modified. For each band, you can modify only band height.

This is the sequence of bands in any jasper report (Fig 1.0). You can hide/unhide bands according to your needs.

If you click on any band, you can see the properties of that band in your right hand side panels (Fig 2.0). (If properties panel is not visible, Click on menu Window > Properties.


FIg 1.0

FIg 1.0

Fig 2.0

Fig 2.0






Sharad Sinha

How to Optimize Tmap Component in Talend

How to Optimize Tmap Component in Talend


Optimize Tmap component in Talend

If Lookup table has few rows:

  • Open tmap select lookup model as Load Once .This will load lookup data one time before mail flow starts.
  • This Lookup data will be stored in memory then main flow execution is very fast with comparing with lookup data in memory.

IF lookup table data is very large:

Talend cannont store the lookup table data in memory. you will get java heap space Execption. To resolve this issue follow the below steps

  • open tmap
  • go to lookup table
  • click on tmap settings
  • select the value for store temp data property to True
  • click on ok
  • In Tmap properties basic settings  set Temp Data Directory path by browsing folder
  • Go to Advance settings
  • set  max buffer size(nb of row) to some value based on  lookup condition data type