edu Toidayhoc

Author: alien

  • Khóa học miễn phí PostgreSQL – C/C++ nhận dự án làm có lương

    PostgreSQL – C/C++ Interface

    This tutorial is going to use libpqxx library, which is the official C++ client API for PostgreSQL. The source code for libpqxx is available under the BSD license, so you are free to download it, pass it on to others, change it, sell it, include it in your own code, and share your changes with anyone you choose.

    Installation

    The the latest version of libpqxx is available to be downloaded from the link . So download the latest version and follow the following steps −

    wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

    Before you start using C/C++ PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line −

    # IPv4 local connections:
host    all         all         127.0.0.1/32          md5

    You can start/restart postgres server in case it is not running using the following command −

    [root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

    C/C++ Interface APIs

    The following are important interface routines which can sufice your requirement to work with PostgreSQL database from your C/C++ program. If you are looking for a more sophisticated application then you can look into the libpqxx official documentation, or you can use commercially available APIs.

    S. No. API & Description
    1

    pqxx::connection C( const std::string & dbstring )

    This is a typedef which will be used to connect to the database. Here, dbstring provides required parameters to connect to the datbase, for example dbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432.

    If connection is setup successfully then it creates C with connection object which provides various useful function public function.
    2

    C.is_open()

    The method is_open() is a public method of connection object and returns boolean value. If connection is active, then this method returns true otherwise it returns false.
    3

    C.disconnect()

    This method is used to disconnect an opened database connection.
    4

    pqxx::work W( C )

    This is a typedef which will be used to create a transactional object using connection C, which ultimately will be used to execute SQL statements in transactional mode.

    If transaction object gets created successfully, then it is assigned to variable W which will be used to access public methods related to transactional object.
    5

    W.exec(const std::string & sql)

    This public method from transactional object will be used to execute SQL statement.
    6

    W.commit()

    This public method from transactional object will be used to commit the transaction.
    7

    W.abort()

    This public method from transactional object will be used to rollback the transaction.
    8

    pqxx::nontransaction N( C )

    This is a typedef which will be used to create a non-transactional object using connection C, which ultimately will be used to execute SQL statements in non-transactional mode.

    If transaction object gets created successfully, then it is assigned to variable N which will be used to access public methods related to non-transactional object.
    9

    N.exec(const std::string & sql)

    This public method from non-transactional object will be used to execute SQL statement and returns a result object which is actually an interator holding all the returned records.

    Connecting To Database

    The following C code segment shows how to connect to an existing database running on local machine at port 5432. Here, I used backslash for line continuation.

    #include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   try {
      connection C("dbname = testdb user = postgres password = cohondob
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can''t open database" << endl;
         return 1;
      }
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

    Now, let us compile and run the above program to connect to our database testdb, which is already available in your schema and can be accessed using user postgres and password pass123.

    You can use the user ID and password based on your database setting. Remember to keep the -lpqxx and -lpq in the given order! Otherwise, the linker will complain bitterly about the missing functions with names starting with “PQ.”

    $g++ test.cpp -lpqxx -lpq
$./a.out
Opened database successfully: testdb

    Create a Table

    The following C code segment will be used to create a table in previously created database −

    #include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can''t open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "CREATE TABLE COMPANY("
      "ID INT PRIMARY KEY     NOT NULL,"
      "NAME           TEXT    NOT NULL,"
      "AGE            INT     NOT NULL,"
      "ADDRESS        CHAR(50),"
      "SALARY         REAL );";

      /* Create a transactional object. */
      work W(C);

      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

    When the above given program is compiled and executed, it will create COMPANY table in your testdb database and will display the following statements −

    Opened database successfully: testdb
Table created successfully

    INSERT Operation

    The following C code segment shows how we can create records in our COMPANY table created in above example −

    #include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can''t open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
         "VALUES (1, ''Paul'', 32, ''California'', 20000.00 ); "
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
         "VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 ); "
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
         "VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 );"
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
         "VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 );";

      /* Create a transactional object. */
      work W(C);

      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

    When the above given program is compiled and executed, it will create given records in COMPANY table and will display the following two lines −

    Opened database successfully: testdb
Records created successfully

    SELECT Operation

    The following C code segment shows how we can fetch and display records from our COMPANY table created in above example −

    #include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can''t open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);

      /* Execute SQL query */
      result R( N.exec( sql ));

      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

    When the above given program is compiled and executed, it will produce the following result −

    Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully

    UPDATE Operation

    The following C code segment shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

    #include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can''t open database" << endl;
         return 1;
      }

      /* Create a transactional object. */
      work W(C);
      /* Create  SQL UPDATE statement */
      sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;

      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);

      /* Execute SQL query */
      result R( N.exec( sql ));

      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

    When the above given program is compiled and executed, it will produce the following result −

    Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

    DELETE Operation

    The following C code segment shows how we can use the DELETE statement to delete any record and then fetch and display remaining records from our COMPANY table −

    #include <iostream>
#include <pqxx/pqxx>

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;

   try {
      connection C("dbname = testdb user = postgres password = cohondob
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can''t open database" << endl;
         return 1;
      }

      /* Create a transactional object. */
      work W(C);
      /* Create  SQL DELETE statement */
      sql = "DELETE from COMPANY where ID = 2";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;

      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);

      /* Execute SQL query */
      result R( N.exec( sql ));

      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

    When the above given program is compiled and executed, it will produce the following result −

    Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – PHP nhận dự án làm có lương

    PostgreSQL – PHP Interface

    Installation

    The PostgreSQL extension is enabled by default in the latest releases of PHP 5.3.x. It is possible to disable it by using –without-pgsql at compile time. Still you can use yum command to install PHP -PostgreSQL interface −

    yum install php-pgsql

    Before you start using the PHP PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line −

    # IPv4 local connections:
host    all         all         127.0.0.1/32          md5

    You can start/restart the postgres server, in case it is not running, using the following command −

    [root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

    Windows users must enable php_pgsql.dll in order to use this extension. This DLL is included with Windows distributions in the latest releases of PHP 5.3.x

    For detailed installation instructions, kindly check our PHP tutorial and its official website.

    PHP Interface APIs

    The following are important PHP routines, which can suffice your requirement to work with PostgreSQL database from your PHP program. If you are looking for a more sophisticated application, then you can look into the PHP official documentation.

    S. No. API & Description
    1

    resource pg_connect ( string $connection_string [, int $connect_type ] )

    This opens a connection to a PostgreSQL database specified by the connection_string.

    If PGSQL_CONNECT_FORCE_NEW is passed as connect_type, then a new connection is created in case of a second call to pg_connect(), even if the connection_string is identical to an existing connection.
    2

    bool pg_connection_reset ( resource $connection )

    This routine resets the connection. It is useful for error recovery. Returns TRUE on success or FALSE on failure.
    3

    int pg_connection_status ( resource $connection )

    This routine returns the status of the specified connection. Returns PGSQL_CONNECTION_OK or PGSQL_CONNECTION_BAD.
    4

    string pg_dbname ([ resource $connection ] )

    This routine returns the name of the database that the given PostgreSQL connection resource.
    5

    resource pg_prepare ([ resource $connection ], string $stmtname, string $query )

    This submits a request to create a prepared statement with the given parameters and waits for completion.
    6

    resource pg_execute ([ resource $connection ], string $stmtname, array $params )

    This routine sends a request to execute a prepared statement with given parameters and waits for the result.
    7

    resource pg_query ([ resource $connection ], string $query )

    This routine executes the query on the specified database connection.
    8

    array pg_fetch_row ( resource $result [, int $row ] )

    This routine fetches one row of data from the result associated with the specified result resource.
    9

    array pg_fetch_all ( resource $result )

    This routine returns an array that contains all rows (records) in the result resource.
    10

    int pg_affected_rows ( resource $result )

    This routine returns the number of rows affected by INSERT, UPDATE, and DELETE queries.
    11

    int pg_num_rows ( resource $result )

    This routine returns the number of rows in a PostgreSQL result resource for example number of rows returned by SELECT statement.
    12

    bool pg_close ([ resource $connection ] )

    This routine closes the non-persistent connection to a PostgreSQL database associated with the given connection resource.
    13

    string pg_last_error ([ resource $connection ] )

    This routine returns the last error message for a given connection.
    14

    string pg_escape_literal ([ resource $connection ], string $data )

    This routine escapes a literal for insertion into a text field.
    15

    string pg_escape_string ([ resource $connection ], string $data )

    This routine escapes a string for querying the database.

    Connecting to Database

    The following PHP code shows how to connect to an existing database on a local machine and finally a database connection object will be returned.

    <?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open databasen";
   } else {
      echo "Opened database successfullyn";
   }
?>

    Now, let us run the above given program to open our database testdb: if the database is successfully opened, then it will give the following message −

    Opened database successfully

    Create a Table

    The following PHP program will be used to create a table in a previously created database −

    <?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open databasen";
   } else {
      echo "Opened database successfullyn";
   }

   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
   } else {
      echo "Table created successfullyn";
   }
   pg_close($db);
?>

    When the above given program is executed, it will create COMPANY table in your testdb and it will display the following messages −

    Opened database successfully
Table created successfully

    INSERT Operation

    The following PHP program shows how we can create records in our COMPANY table created in above example −

    <?php
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open databasen";
   } else {
      echo "Opened database successfullyn";
   }

   $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, ''Paul'', 32, ''California'', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 );
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
   } else {
      echo "Records created successfullyn";
   }
   pg_close($db);
?>

    When the above given program is executed, it will create the given records in COMPANY table and will display the following two lines −

    Opened database successfully
Records created successfully

    SELECT Operation

    The following PHP program shows how we can fetch and display records from our COMPANY table created in above example −

    <?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open databasen";
   } else {
      echo "Opened database successfullyn";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   }
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "n";
      echo "NAME = ". $row[1] ."n";
      echo "ADDRESS = ". $row[2] ."n";
      echo "SALARY =  ".$row[4] ."nn";
   }
   echo "Operation done successfullyn";
   pg_close($db);
?>

    When the above given program is executed, it will produce the following result. Keep a note that fields are returned in the sequence they were used while creating table.

    Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

    UPDATE Operation

    The following PHP code shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

    <?php
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open databasen";
   } else {
      echo "Opened database successfullyn";
   }
   $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } else {
      echo "Record updated successfullyn";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   }
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "n";
      echo "NAME = ". $row[1] ."n";
      echo "ADDRESS = ". $row[2] ."n";
      echo "SALARY =  ".$row[4] ."nn";
   }
   echo "Operation done successfullyn";
   pg_close($db);
?>

    When the above given program is executed, it will produce the following result −

    Opened database successfully
Record updated successfully
ID = 2
NAME = Allen
ADDRESS = 25
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

    DELETE Operation

    The following PHP code shows how we can use the DELETE statement to delete any record and then fetch and display the remaining records from our COMPANY table −

    <?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5432";
   $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open databasen";
   } else {
      echo "Opened database successfullyn";
   }
   $sql =<<<EOF
      DELETE from COMPANY where ID=2;
EOF;
   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } else {
      echo "Record deleted successfullyn";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   }
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "n";
      echo "NAME = ". $row[1] ."n";
      echo "ADDRESS = ". $row[2] ."n";
      echo "SALARY =  ".$row[4] ."nn";
   }
   echo "Operation done successfullyn";
   pg_close($db);
?>

    When the above given program is executed, it will produce the following result −

    Opened database successfully
Record deleted successfully
ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Java nhận dự án làm có lương

    PostgreSQL – JAVA Interface

    Installation

    Before we start using PostgreSQL in our Java programs, we need to make sure that we have PostgreSQL JDBC and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now let us check how to set up PostgreSQL JDBC driver.

    • Download the latest version of postgresql-(VERSION).jdbc.jar from repository.

    • Add downloaded jar file postgresql-(VERSION).jdbc.jar in your class path, or you can use it along with -classpath option as explained below in the examples.

    The following section assumes you have little knowledge about Java JDBC concepts. If you do not have, then it is suggested to spent half and hour with to become comfortable with concepts explained below.

    Connecting To Database

    The following Java code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

    import java.sql.Connection;
import java.sql.DriverManager;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "postgres", "123");
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

    Before you compile and run above program, find pg_hba.conf file in your PostgreSQL installation directory and add the following line −

    # IPv4 local connections:
host    all         all         127.0.0.1/32          md5

    You can start/restart the postgres server in case it is not running using the following command −

    [root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

    Now, let us compile and run the above program to connect with testdb. Here, we are using postgres as user ID and 123 as password to access the database. You can change this as per your database configuration and setup. We are also assuming current version of JDBC driver postgresql-9.2-1002.jdbc3.jar is available in the current path.

    C:JavaPostgresIntegration>javac PostgreSQLJDBC.java
C:JavaPostgresIntegration>java -cp c:toolspostgresql-9.2-1002.jdbc3.jar;C:JavaPostgresIntegration PostgreSQLJDBC
Open database successfully

    Create a Table

    The following Java program will be used to create a table in previously opened database. Make sure you do not have this table already in your target database.

    import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

    When a program is compiled and executed, it will create the COMPANY table in testdb database and will display the following two lines −

    Opened database successfully
Table created successfully

    INSERT Operation

    The following Java program shows how we can create records in our COMPANY table created in above example −

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, ''Paul'', 32, ''California'', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

    When the above program is compiled and executed, it will create given records in COMPANY table and will display the following two lines −

    Opened database successfully
Records created successfully

    SELECT Operation

    The following Java program shows how we can fetch and display records from our COMPANY table created in above example −

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

    When the program is compiled and executed, it will produce the following result −

    Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

    UPDATE Operation

    The following Java code shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

    When the program is compiled and executed, it will produce the following result −

    Opened database successfully
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Operation done successfully

    DELETE Operation

    The following Java code shows how we can use the DELETE statement to delete any record and then fetch and display remaining records from our COMPANY table −

    import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC6 {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID = 2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

    When the program is compiled and executed, it will produce the following result −

    Opened database successfully
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Operation done successfully

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Perl nhận dự án làm có lương

    PostgreSQL – Perl Interface

    Installation

    The PostgreSQL can be integrated with Perl using Perl DBI module, which is a database access module for the Perl programming language. It defines a set of methods, variables and conventions that provide a standard database interface.

    Here are simple steps to install DBI module on your Linux/Unix machine −

    $ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625
$ perl Makefile.PL
$ make
$ make install

    If you need to install SQLite driver for DBI, then it can be installed as follows −

    $ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz
$ cd DBD-Pg-2.19.3
$ perl Makefile.PL
$ make
$ make install

    Before you start using Perl PostgreSQL interface, find the pg_hba.conf file in your PostgreSQL installation directory and add the following line −

    # IPv4 local connections:
host    all         all         127.0.0.1/32          md5

    You can start/restart the postgres server, in case it is not running, using the following command −

    [root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

    DBI Interface APIs

    Following are the important DBI routines, which can suffice your requirement to work with SQLite database from your Perl program. If you are looking for a more sophisticated application, then you can look into Perl DBI official documentation.

    S. No. API & Description
    1

    DBI→connect($data_source, “userid”, “password”, %attr)

    Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds.

    Datasource has the form like : DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg is PostgreSQL driver name and testdb is the name of database.
    2

    $dbh→do($sql)

    This routine prepares and executes a single SQL statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. Here $dbh is a handle returned by DBI→connect() call.
    3

    $dbh→prepare($sql)

    This routine prepares a statement for later execution by the database engine and returns a reference to a statement handle object.
    4

    $sth→execute()

    This routine performs whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected. Here $sth is a statement handle returned by $dbh→prepare($sql) call.
    5

    $sth→fetchrow_array()

    This routine fetches the next row of data and returns it as a list containing the field values. Null fields are returned as undef values in the list.
    6

    $DBI::err

    This is equivalent to $h→err, where $h is any of the handle types like $dbh, $sth, or $drh. This returns native database engine error code from the last driver method called.
    7

    $DBI::errstr

    This is equivalent to $h→errstr, where $h is any of the handle types like $dbh, $sth, or $drh. This returns the native database engine error message from the last DBI method called.
    8

    $dbh->disconnect()

    This routine closes a database connection previously opened by a call to DBI→connect().

    Connecting to Database

    The following Perl code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

    #!/usr/bin/perl

use DBI;
use strict;

my $driver  = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;

print "Opened database successfullyn";

    Now, let us run the above given program to open our database testdb; if the database is successfully opened then it will give the following message −

    Open database successfully

    Create a Table

    The following Perl program will be used to create a table in previously created database −

    #!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfullyn";

my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
   print $DBI::errstr;
} else {
   print "Table created successfullyn";
}
$dbh->disconnect();

    When the above given program is executed, it will create COMPANY table in your testdb and it will display the following messages −

    Opened database successfully
Table created successfully

    INSERT Operation

    The following Perl program shows how we can create records in our COMPANY table created in above example −

    #!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfullyn";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (1, ''Paul'', 32, ''California'', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfullyn";
$dbh->disconnect();

    When the above given program is executed, it will create given records in COMPANY table and will display the following two lines −

    Opened database successfully
Records created successfully

    SELECT Operation

    The following Perl program shows how we can fetch and display records from our COMPANY table created in above example −

    #!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfullyn";

my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "n";
      print "NAME = ". $row[1] ."n";
      print "ADDRESS = ". $row[2] ."n";
      print "SALARY =  ". $row[3] ."nn";
}
print "Operation done successfullyn";
$dbh->disconnect();

    When the above given program is executed, it will produce the following result −

    Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

    UPDATE Operation

    The following Perl code shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

    #!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfullyn";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
   print $DBI::errstr;
}else{
   print "Total number of rows updated : $rvn";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "n";
      print "NAME = ". $row[1] ."n";
      print "ADDRESS = ". $row[2] ."n";
      print "SALARY =  ". $row[3] ."nn";
}
print "Operation done successfullyn";
$dbh->disconnect();

    When the above given program is executed, it will produce the following result −

    Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

    DELETE Operation

    The following Perl code shows how we can use the DELETE statement to delete any record and then fetch and display the remaining records from our COMPANY table −

    #!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg";
my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres";
my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr;
print "Opened database successfullyn";

my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
   print $DBI::errstr;
} else{
   print "Total number of rows deleted : $rvn";
}
$stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
   print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "n";
      print "NAME = ". $row[1] ."n";
      print "ADDRESS = ". $row[2] ."n";
      print "SALARY =  ". $row[3] ."nn";
}
print "Operation done successfullyn";
$dbh->disconnect();

    When the above given program is executed, it will produce the following result −

    Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Useful Functions nhận dự án làm có lương

    PostgreSQL – Useful Functions

    PostgreSQL built-in functions, also called as Aggregate functions, are used for performing processing on string or numeric data.

    The following is the list of all general-purpose PostgreSQL built-in functions −

    • − The PostgreSQL COUNT aggregate function is used to count the number of rows in a database table.

    • − The PostgreSQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.

    • − The PostgreSQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

    • − The PostgreSQL AVG aggregate function selects the average value for certain table column.

    • − The PostgreSQL SUM aggregate function allows selecting the total for a numeric column.

    • − The PostgreSQL ARRAY aggregate function puts input values, including nulls, concatenated into an array.

    • − Complete list of PostgreSQL functions required to manipulate numbers in SQL.

    • − Complete list of PostgreSQL functions required to manipulate strings in PostgreSQL.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Functions nhận dự án làm có lương

    PostgreSQL – Functions

    PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.

    Functions can be created in a language of your choice like SQL, PL/pgSQL, C, Python, etc.

    Syntax

    The basic syntax to create a function is as follows −

    CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [...]
      RETURN { variable_name | value }
   END; LANGUAGE plpgsql;

    Where,

    • function-name specifies the name of the function.

    • [OR REPLACE] option allows modifying an existing function.

    • The function must contain a return statement.

    • RETURN clause specifies that data type you are going to return from the function. The return_datatype can be a base, composite, or domain type, or can reference the type of a table column.

    • function-body contains the executable part.

    • The AS keyword is used for creating a standalone function.

    • plpgsql is the name of the language that the function is implemented in. Here, we use this option for PostgreSQL, it Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes.

    Example

    The following example illustrates creating and calling a standalone function. This function returns the total number of records in the COMPANY table. We will use the table, which has the following records −

    testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

    Function totalRecords() is as follows −

    CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
	total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

    When the above query is executed, the result would be −

    testdb# CREATE FUNCTION

    Now, let us execute a call to this function and check the records in the COMPANY table

    testdb=# select totalRecords();

    When the above query is executed, the result would be −

     totalrecords
--------------
      7
(1 row)

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí Date/Time Functions & Operators nhận dự án làm có lương

    PostgreSQL – DATE/TIME Functions and Operators

    We had discussed about the Date/Time data types in the chapter . Now, let us see the Date/Time operators and Functions.

    The following table lists the behaviors of the basic arithmetic operators −

    Operator Example Result
    + date ”2001-09-28” + integer ”7” date ”2001-10-05”
    + date ”2001-09-28” + interval ”1 hour” timestamp ”2001-09-28 01:00:00”
    + date ”2001-09-28” + time ”03:00” timestamp ”2001-09-28 03:00:00”
    + interval ”1 day” + interval ”1 hour” interval ”1 day 01:00:00”
    + timestamp ”2001-09-28 01:00” + interval ”23 hours” timestamp ”2001-09-29 00:00:00”
    + time ”01:00” + interval ”3 hours” time ”04:00:00”
    – – interval ”23 hours” interval ”-23:00:00”
    – date ”2001-10-01” – date ”2001-09-28” integer ”3” (days)
    – date ”2001-10-01” – integer ”7” date ”2001-09-24”
    – date ”2001-09-28” – interval ”1 hour” timestamp ”2001-09-27 23:00:00”
    – time ”05:00” – time ”03:00” interval ”02:00:00”
    – time ”05:00” – interval ”2 hours” time ”03:00:00”
    – timestamp ”2001-09-28 23:00” – interval ”23 hours” timestamp ”2001-09-28 00:00:00”
    – interval ”1 day” – interval ”1 hour” interval ”1 day -01:00:00”
    – timestamp ”2001-09-29 03:00” – timestamp ”2001-09-27 12:00” interval ”1 day 15:00:00”
    * 900 * interval ”1 second” interval ”00:15:00”
    * 21 * interval ”1 day” interval ”21 days”
    * double precision ”3.5” * interval ”1 hour” interval ”03:30:00”
    / interval ”1 hour” / double precision ”1.5” interval ”00:40:00”

    The following is the list of all important Date and Time related functions available.

    S. No. Function & Description
    1

    Subtract arguments
    2

    Current date and time
    3

    Get subfield (equivalent to extract)
    4

    Get subfield
    5

    Test for finite date, time and interval (not +/-infinity)
    6

    Adjust interval

    AGE(timestamp, timestamp), AGE(timestamp)

    S. No. Function & Description
    1

    AGE(timestamp, timestamp)

    When invoked with the TIMESTAMP form of the second argument, AGE() subtract arguments, producing a “symbolic” result that uses years and months and is of type INTERVAL.
    2

    AGE(timestamp)

    When invoked with only the TIMESTAMP as argument, AGE() subtracts from the current_date (at midnight).

    Example of the function AGE(timestamp, timestamp) is −

    testdb=# SELECT AGE(timestamp ''2001-04-10'', timestamp ''1957-06-13'');

    The above given PostgreSQL statement will produce the following result −

               age
-------------------------
 43 years 9 mons 27 days

    Example of the function AGE(timestamp) is −

    testdb=# select age(timestamp ''1957-06-13'');

    The above given PostgreSQL statement will produce the following result −

               age
--------------------------
 55 years 10 mons 22 days

    CURRENT DATE/TIME()

    PostgreSQL provides a number of functions that return values related to the current date and time. Following are some functions −

    S. No. Function & Description
    1

    CURRENT_DATE

    Delivers current date.
    2

    CURRENT_TIME

    Delivers values with time zone.
    3

    CURRENT_TIMESTAMP

    Delivers values with time zone.
    4

    CURRENT_TIME(precision)

    Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field.
    5

    CURRENT_TIMESTAMP(precision)

    Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field.
    6

    LOCALTIME

    Delivers values without time zone.
    7

    LOCALTIMESTAMP

    Delivers values without time zone.
    8

    LOCALTIME(precision)

    Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field.
    9

    LOCALTIMESTAMP(precision)

    Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field.

    Examples using the functions from the table above −

    testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)


testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)


testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

    PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. These functions are −

    S. No. Function & Description
    1

    transaction_timestamp()

    It is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns.
    2

    statement_timestamp()

    It returns the start time of the current statement.
    3

    clock_timestamp()

    It returns the actual current time, and therefore its value changes even within a single SQL command.
    4

    timeofday()

    It returns the actual current time, but as a formatted text string rather than a timestamp with time zone value.
    5

    now()

    It is a traditional PostgreSQL equivalent to transaction_timestamp().

    DATE_PART(text, timestamp), DATE_PART(text, interval), DATE_TRUNC(text, timestamp)

    S. No. Function & Description
    1

    DATE_PART(”field”, source)

    These functions get the subfields. The field parameter needs to be a string value, not a name.

    The valid field names are: century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year.
    2

    DATE_TRUNC(”field”, source)

    This function is conceptually similar to the trunc function for numbers. source is a value expression of type timestamp or interval. field selects to which precision to truncate the input value. The return value is of type timestamp or interval.

    The valid values for field are : microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium

    The following are examples for DATE_PART(”field”, source) functions −

    testdb=# SELECT date_part(''day'', TIMESTAMP ''2001-02-16 20:38:40'');
 date_part
-----------
        16
(1 row)


testdb=# SELECT date_part(''hour'', INTERVAL ''4 hours 3 minutes'');
 date_part
-----------
         4
(1 row)

    The following are examples for DATE_TRUNC(”field”, source) functions −

    testdb=# SELECT date_trunc(''hour'', TIMESTAMP ''2001-02-16 20:38:40'');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)


testdb=# SELECT date_trunc(''year'', TIMESTAMP ''2001-02-16 20:38:40'');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

    EXTRACT(field from timestamp), EXTRACT(field from interval)

    The EXTRACT(field FROM source) function retrieves subfields such as year or hour from date/time values. The source must be a value expression of type timestamp, time, or interval. The field is an identifier or string that selects what field to extract from the source value. The EXTRACT function returns values of type double precision.

    The following are valid field names (similar to DATE_PART function field names): century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year.

    The following are examples of EXTRACT(”field”, source) functions −

    testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP ''2000-12-16 12:21:13'');
 date_part
-----------
        20
(1 row)


testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP ''2001-02-16 20:38:40'');
 date_part
-----------
        16
(1 row)

    ISFINITE(date), ISFINITE(timestamp), ISFINITE(interval)

    S. No. Function & Description
    1

    ISFINITE(date)

    Tests for finite date.
    2

    ISFINITE(timestamp)

    Tests for finite time stamp.
    3

    ISFINITE(interval)

    Tests for finite interval.

    The following are the examples of the ISFINITE() functions −

    testdb=# SELECT isfinite(date ''2001-02-16'');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(timestamp ''2001-02-16 21:28:30'');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(interval ''4 hours'');
 isfinite
----------
 t
(1 row)

    JUSTIFY_DAYS(interval), JUSTIFY_HOURS(interval), JUSTIFY_INTERVAL(interval)

    S. No. Function & Description
    1

    JUSTIFY_DAYS(interval)

    Adjusts interval so 30-day time periods are represented as months. Return the interval type
    2

    JUSTIFY_HOURS(interval)

    Adjusts interval so 24-hour time periods are represented as days. Return the interval type
    3

    JUSTIFY_INTERVAL(interval)

    Adjusts interval using JUSTIFY_DAYS and JUSTIFY_HOURS, with additional sign adjustments. Return the interval type

    The following are the examples for the ISFINITE() functions −

    testdb=# SELECT justify_days(interval ''35 days'');
 justify_days
--------------
 1 mon 5 days
(1 row)


testdb=# SELECT justify_hours(interval ''27 hours'');
 justify_hours
----------------
 1 day 03:00:00
(1 row)


testdb=# SELECT justify_interval(interval ''1 mon -1 hour'');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Privileges nhận dự án làm có lương

    PostgreSQL – PRIVILEGES

    Whenever an object is created in a database, an owner is assigned to it. The owner is usually the one who executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can modify or delete the object. To allow other roles or users to use it, privileges or permission must be granted.

    Different kinds of privileges in PostgreSQL are −

    • SELECT,
    • INSERT,
    • UPDATE,
    • DELETE,
    • TRUNCATE,
    • REFERENCES,
    • TRIGGER,
    • CREATE,
    • CONNECT,
    • TEMPORARY,
    • EXECUTE, and
    • USAGE

    Depending on the type of the object (table, function, etc.,), privileges are applied to the object. To assign privileges to the users, the GRANT command is used.

    Syntax for GRANT

    Basic syntax for GRANT command is as follows −

    GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

    • privilege − values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

    • object − The name of an object to which to grant access. The possible objects are: table, view, sequence

    • PUBLIC − A short form representing all users.

    • GROUP group − A group to whom to grant privileges.

    • username − The name of a user to whom to grant privileges. PUBLIC is a short form representing all users.

    The privileges can be revoked using the REVOKE command.

    Syntax for REVOKE

    Basic syntax for REVOKE command is as follows −

    REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

    • privilege − values could be: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.

    • object − The name of an object to which to grant access. The possible objects are: table, view, sequence

    • PUBLIC − A short form representing all users.

    • GROUP group − A group to whom to grant privileges.

    • username − The name of a user to whom to grant privileges. PUBLIC is a short form representing all users.

    Example

    To understand the privileges, let us first create a USER as follows −

    testdb=# CREATE USER manisha WITH PASSWORD ''password
CREATE ROLE

    The message CREATE ROLE indicates that the USER “manisha” is created.

    Consider the table having records as follows −

    testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

    Next, let us grant all privileges on a table COMPANY to the user “manisha” as follows −

    testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT

    The message GRANT indicates that all privileges are assigned to the USER.

    Next, let us revoke the privileges from the USER “manisha” as follows −

    testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE

    The message REVOKE indicates that all privileges are revoked from the USER.

    You can even delete the user as follows −

    testdb=# DROP USER manisha;
DROP ROLE

    The message DROP ROLE indicates USER ‘Manisha’ is deleted from the database.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Auto Increment nhận dự án làm có lương

    PostgreSQL – AUTO INCREMENT

    PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.

    If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.

    The type name serial creates an integer columns. The type name bigserial creates a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type name smallserial creates a smallint column.

    Syntax

    The basic usage of SERIAL dataype is as follows −

    CREATE TABLE tablename (
   colname SERIAL
);

    Example

    Consider the COMPANY table to be created as follows −

    testdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

    Now, insert the following records into table COMPANY −

    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( ''Paul'', 32, ''California'', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES (''Allen'', 25, ''Texas'', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES (''Teddy'', 23, ''Norway'', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( ''Mark'', 25, ''Rich-Mond '', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( ''David'', 27, ''Texas'', 85000.00 );


INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( ''Kim'', 22, ''South-Hall'', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( ''James'', 24, ''Houston'', 10000.00 );

    This will insert seven tuples into the table COMPANY and COMPANY will have the following records −

     id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí PostgreSQL – Sub Queries nhận dự án làm có lương

    PostgreSQL – Sub Queries

    A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause.

    A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

    Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

    There are a few rules that subqueries must follow −

    • Subqueries must be enclosed within parentheses.

    • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.

    • An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

    • Subqueries that return more than one row can only be used with multiple value operators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator.

    • The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.

    Subqueries with the SELECT Statement

    Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

    SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

    Example

    Consider the table having the following records −

     id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

    Now, let us check the following sub-query with SELECT statement −

    testdb=# SELECT *
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

    This would produce the following result −

     id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)

    Subqueries with the INSERT Statement

    Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

    The basic syntax is as follows −

    INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

    Example

    Consider a table COMPANY_BKP, with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. Now, to copy complete COMPANY table into COMPANY_BKP, following is the syntax −

    testdb=# INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

    Subqueries with the UPDATE Statement

    The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

    The basic syntax is as follows −

    UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

    Example

    Assuming, we have COMPANY_BKP table available, which is backup of the COMPANY table.

    The following example updates SALARY by 0.50 times in the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

    testdb=# UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

    This would affect two rows and finally the COMPANY table would have the following records −

     id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

    Subqueries with the DELETE Statement

    The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

    The basic syntax is as follows −

    DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

    Example

    Assuming, we have COMPANY_BKP table available, which is a backup of the COMPANY table.

    The following example deletes records from the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

    testdb=# DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE > 27 );

    This would affect two rows and finally the COMPANY table would have the following records −

     id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc