• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • LIBPQ   -Library Pooled Quota

      LIBPQ   -Library Pooled Quota

      • Posted by Nijamutheen J
      • Categories Blog
      • Date January 16, 2019
      • Comments 0 comment
      LIBPQ   -Library Pooled Quota
      •  Details About Connected Users Using tools
      •  libpq is the C application programmer’s interface to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.
      • Client programs that use libpq must include the header file libpq-fe.h and must link with the libpq library
      • Here are also several complete examples of libpq applications in the directory src/test/examples in the source code distribution.
      libpq Example Program 1

      /*
       * testlibpq.c
       *
       *      Test the C version of libpq, the PostgreSQL frontend library.
       */
      #include <stdio.h>
      #include <stdlib.h>
      #include <libpq-fe.h>
      
      static void
      exit_nicely(PGconn *conn)
      {
          PQfinish(conn);
          exit(1);
      }
      
      int
      main(int argc, char **argv)
      {
          const char *conninfo;
          PGconn     *conn;
          PGresult   *res;
          int         nFields;
          int         i,
                      j;
      
          /*
           * If the user supplies a parameter on the command line, use it as the
           * conninfo string; otherwise default to setting dbname=postgres and using
           * environment variables or defaults for all other connection parameters.
           */
          if (argc > 1)
              conninfo = argv[1];
          else
              conninfo = "dbname = postgres";
      
          /* Make a connection to the database */
          conn = PQconnectdb(conninfo);
      
          /* Check to see that the backend connection was successfully made */
          if (PQstatus(conn) != CONNECTION_OK)
          {
              fprintf(stderr, "Connection to database failed: %s",
                      PQerrorMessage(conn));
              exit_nicely(conn);
          }
      
          /*
           * Our test case here involves using a cursor, for which we must be inside
           * a transaction block.  We could do the whole thing with a single
           * PQexec() of "select * from pg_database", but that's too trivial to make
           * a good example.
           */
      
          /* Start a transaction block */
          res = PQexec(conn, "BEGIN");
          if (PQresultStatus(res) != PGRES_COMMAND_OK)
          {
              fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          /*
           * Should PQclear PGresult whenever it is no longer needed to avoid memory
           * leaks
           */
          PQclear(res);
      
          /*
           * Fetch rows from pg_database, the system catalog of databases
           */
          res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
          if (PQresultStatus(res) != PGRES_COMMAND_OK)
          {
              fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
          PQclear(res);
      
          res = PQexec(conn, "FETCH ALL in myportal");
          if (PQresultStatus(res) != PGRES_TUPLES_OK)
          {
              fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          /* first, print out the attribute names */
          nFields = PQnfields(res);
          for (i = 0; i < nFields; i++)
              printf("%-15s", PQfname(res, i));
          printf("\n\n");
      
          /* next, print out the rows */
          for (i = 0; i < PQntuples(res); i++)
          {
              for (j = 0; j < nFields; j++)
                  printf("%-15s", PQgetvalue(res, i, j));
              printf("\n");
          }
      
          PQclear(res);
      
          /* close the portal ... we don't bother to check for errors ... */
          res = PQexec(conn, "CLOSE myportal");
          PQclear(res);
      
          /* end the transaction */
          res = PQexec(conn, "END");
          PQclear(res);
      
          /* close the connection to the database and cleanup */
          PQfinish(conn);
      
          return 0;
      }
       libpq Example Program 2

      /*
       * testlibpq2.c
       *      Test of the asynchronous notification interface
       *
       * Start this program, then from psql in another window do
       *   NOTIFY TBL2;
       * Repeat four times to get this program to exit.
       *
       * Or, if you want to get fancy, try this:
       * populate a database with the following commands
       * (provided in src/test/examples/testlibpq2.sql):
       *
       *   CREATE TABLE TBL1 (i int4);
       *
       *   CREATE TABLE TBL2 (i int4);
       *
       *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
       *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
       *
       * and do this four times:
       *
       *   INSERT INTO TBL1 VALUES (10);
       */
      #include <stdio.h>
      #include <stdlib.h>
      #include <string.h>
      #include <errno.h>
      #include <sys/time.h>
      #include <libpq-fe.h>
      
      static void
      exit_nicely(PGconn *conn)
      {
          PQfinish(conn);
          exit(1);
      }
      
      int
      main(int argc, char **argv)
      {
          const char *conninfo;
          PGconn     *conn;
          PGresult   *res;
          PGnotify   *notify;
          int         nnotifies;
      
          /*
           * If the user supplies a parameter on the command line, use it as the
           * conninfo string; otherwise default to setting dbname=postgres and using
           * environment variables or defaults for all other connection parameters.
           */
          if (argc > 1)
              conninfo = argv[1];
          else
              conninfo = "dbname = postgres";
      
          /* Make a connection to the database */
          conn = PQconnectdb(conninfo);
      
          /* Check to see that the backend connection was successfully made */
          if (PQstatus(conn) != CONNECTION_OK)
          {
              fprintf(stderr, "Connection to database failed: %s",
                      PQerrorMessage(conn));
              exit_nicely(conn);
          }
      
          /*
           * Issue LISTEN command to enable notifications from the rule's NOTIFY.
           */
          res = PQexec(conn, "LISTEN TBL2");
          if (PQresultStatus(res) != PGRES_COMMAND_OK)
          {
              fprintf(stderr, "LISTEN command failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          /*
           * should PQclear PGresult whenever it is no longer needed to avoid memory
           * leaks
           */
          PQclear(res);
      
          /* Quit after four notifies are received. */
          nnotifies = 0;
          while (nnotifies < 4)
          {
              /*
               * Sleep until something happens on the connection.  We use select(2)
               * to wait for input, but you could also use poll() or similar
               * facilities.
               */
              int         sock;
              fd_set      input_mask;
      
              sock = PQsocket(conn);
      
              if (sock < 0)
                  break;              /* shouldn't happen */
      
              FD_ZERO(&input_mask);
              FD_SET(sock, &input_mask);
      
              if (select(sock + 1, &input_mask, NULL, NULL, NULL) < 0)
              {
                  fprintf(stderr, "select() failed: %s\n", strerror(errno));
                  exit_nicely(conn);
              }
      
              /* Now check for input */
              PQconsumeInput(conn);
              while ((notify = PQnotifies(conn)) != NULL)
              {
                  fprintf(stderr,
                          "ASYNC NOTIFY of '%s' received from backend PID %d\n",
                          notify->relname, notify->be_pid);
                  PQfreemem(notify);
                  nnotifies++;
              }
          }
      
          fprintf(stderr, "Done.\n");
      
          /* close the connection to the database and cleanup */
          PQfinish(conn);
      
          return 0;
      }
      libpq Example Program 3

      /*
       * testlibpq3.c
       *      Test out-of-line parameters and binary I/O.
       *
       * Before running this, populate a database with the following commands
       * (provided in src/test/examples/testlibpq3.sql):
       *
       * CREATE TABLE test1 (i int4, t text, b bytea);
       *
       * INSERT INTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');
       * INSERT INTO test1 values (2, 'ho there', '\\004\\003\\002\\001\\000');
       *
       * The expected output is:
       *
       * tuple 0: got
       *  i = (4 bytes) 1
       *  t = (11 bytes) 'joe's place'
       *  b = (5 bytes) \000\001\002\003\004
       *
       * tuple 0: got
       *  i = (4 bytes) 2
       *  t = (8 bytes) 'ho there'
       *  b = (5 bytes) \004\003\002\001\000
       */
      #include <stdio.h>
      #include <stdlib.h>
      #include <string.h>
      #include <sys/types.h>
      #include <libpq-fe.h>
      
      /* for ntohl/htonl */
      #include <netinet/in.h>
      #include <arpa/inet.h>
      
      
      static void
      exit_nicely(PGconn *conn)
      {
          PQfinish(conn);
          exit(1);
      }
      
      /*
       * This function prints a query result that is a binary-format fetch from
       * a table defined as in the comment above.  We split it out because the
       * main() function uses it twice.
       */
      static void
      show_binary_results(PGresult *res)
      {
          int         i,
                      j;
          int         i_fnum,
                      t_fnum,
                      b_fnum;
      
          /* Use PQfnumber to avoid assumptions about field order in result */
          i_fnum = PQfnumber(res, "i");
          t_fnum = PQfnumber(res, "t");
          b_fnum = PQfnumber(res, "b");
      
          for (i = 0; i < PQntuples(res); i++)
          {
              char       *iptr;
              char       *tptr;
              char       *bptr;
              int         blen;
              int         ival;
      
              /* Get the field values (we ignore possibility they are null!) */
              iptr = PQgetvalue(res, i, i_fnum);
              tptr = PQgetvalue(res, i, t_fnum);
              bptr = PQgetvalue(res, i, b_fnum);
      
              /*
               * The binary representation of INT4 is in network byte order, which
               * we'd better coerce to the local byte order.
               */
              ival = ntohl(*((uint32_t *) iptr));
      
              /*
               * The binary representation of TEXT is, well, text, and since libpq
               * was nice enough to append a zero byte to it, it'll work just fine
               * as a C string.
               *
               * The binary representation of BYTEA is a bunch of bytes, which could
               * include embedded nulls so we have to pay attention to field length.
               */
              blen = PQgetlength(res, i, b_fnum);
      
              printf("tuple %d: got\n", i);
              printf(" i = (%d bytes) %d\n",
                     PQgetlength(res, i, i_fnum), ival);
              printf(" t = (%d bytes) '%s'\n",
                     PQgetlength(res, i, t_fnum), tptr);
              printf(" b = (%d bytes) ", blen);
              for (j = 0; j < blen; j++)
                  printf("\\%03o", bptr[j]);
              printf("\n\n");
          }
      }
      
      int
      main(int argc, char **argv)
      {
          const char *conninfo;
          PGconn     *conn;
          PGresult   *res;
          const char *paramValues[1];
          int         paramLengths[1];
          int         paramFormats[1];
          uint32_t    binaryIntVal;
      
          /*
           * If the user supplies a parameter on the command line, use it as the
           * conninfo string; otherwise default to setting dbname=postgres and using
           * environment variables or defaults for all other connection parameters.
           */
          if (argc > 1)
              conninfo = argv[1];
          else
              conninfo = "dbname = postgres";
      
          /* Make a connection to the database */
          conn = PQconnectdb(conninfo);
      
          /* Check to see that the backend connection was successfully made */
          if (PQstatus(conn) != CONNECTION_OK)
          {
              fprintf(stderr, "Connection to database failed: %s",
                      PQerrorMessage(conn));
              exit_nicely(conn);
          }
      
          /*
           * The point of this program is to illustrate use of PQexecParams() with
           * out-of-line parameters, as well as binary transmission of data.
           *
           * This first example transmits the parameters as text, but receives the
           * results in binary format.  By using out-of-line parameters we can
           * avoid a lot of tedious mucking about with quoting and escaping, even
           * though the data is text.  Notice how we don't have to do anything
           * special with the quote mark in the parameter value.
           */
      
          /* Here is our out-of-line parameter value */
          paramValues[0] = "joe's place";
      
          res = PQexecParams(conn,
                             "SELECT * FROM test1 WHERE t = $1",
                             1,       /* one param */
                             NULL,    /* let the backend deduce param type */
                             paramValues,
                             NULL,    /* don't need param lengths since text */
                             NULL,    /* default to all text params */
                             1);      /* ask for binary results */
      
          if (PQresultStatus(res) != PGRES_TUPLES_OK)
          {
              fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          show_binary_results(res);
      
          PQclear(res);
      
          /*
           * In this second example we transmit an integer parameter in binary
           * form, and again retrieve the results in binary form.
           *
           * Although we tell PQexecParams we are letting the backend deduce
           * parameter type, we really force the decision by casting the parameter
           * symbol in the query text.  This is a good safety measure when sending
           * binary parameters.
           */
      
          /* Convert integer value "2" to network byte order */
          binaryIntVal = htonl((uint32_t) 2);
      
          /* Set up parameter arrays for PQexecParams */
          paramValues[0] = (char *) &binaryIntVal;
          paramLengths[0] = sizeof(binaryIntVal);
          paramFormats[0] = 1;        /* binary */
      
          res = PQexecParams(conn,
                             "SELECT * FROM test1 WHERE i = $1::int4",
                             1,       /* one param */
                             NULL,    /* let the backend deduce param type */
                             paramValues,
                             paramLengths,
                             paramFormats,
                             1);      /* ask for binary results */
      
          if (PQresultStatus(res) != PGRES_TUPLES_OK)
          {
              fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
              PQclear(res);
              exit_nicely(conn);
          }
      
          show_binary_results(res);
      
          PQclear(res);
      
          /* close the connection to the database and cleanup */
          PQfinish(conn);
      
          return 0;
      }

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      Postgresql Caching Memory(About Shared Buffer Access)
      January 16, 2019

      Next post

      How to Tuning Checkpoint Parameters In PostgreSQL
      January 16, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Don’t See What You’re Looking For? We’ll Customize a Package to Fit Your Business Office space!
      14May2022
      Need an Address, Phone Number, And Virtual Office In Chennai?
      14May2022
      Rayafeel Technologies can provide you with ideal meeting solutions
      14May2022
      Virtual Offices VS. Coworking Spaces: What’s The Difference?
      14May2022
      SQL Manager for Oracle Type 6 – 10
      13May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?