pgSQL_Intro1.htm
SQL in PostgreSQL is fabulous
Advantages of PL/pgSQL
  • saving trips between client and server.
  • Process complex and aggregated data for SQL
  • Can use embedded SQL within a function block structure
  • Support plug-in from many programming languages.
  • Capable of returning rows and complex records.
  • Handles  JSON and XML data, for SQL
Power of Select Query in PostgreSQL :
Selecting Array with index:

SELECT (ARRAY['Jan','Feb','Mar'])[2:3];

Selecting a range of array

SELECT (ARRAY[(select dname from dept where deptno = 10),'Feb','Mar', 'Apr'])[1:3];

Using JSON : array_to_json function

SELECT (array_to_json(ARRAY [ARRAY['Jan','1'], ARRAY['Feb','2'] ])) as json1 , (array_to_json(ARRAY [ARRAY['Mar','3'], ARRAY['Apr','4'] ])) as json2

with pgAdmin

Processing XML:

SELECT xml '<IL><C1>Chicago</C1> <C2>Nile</C2></IL>' as IL,'<MN><C1>Roseville</C1> <C2>Blaine</C2></MN>' as MN;

with pgsAdmin command

Note the difference between PHP and pgAdmin queries: PHP