Creating Database Objects

This tutorial describes how to create database objects in PostgreSQL using the TPgSQL and TPgScript components.

1. Requirements
2. General Information
3. Creating Tables
   • 3.1 Design-Time
   • 3.2 Runtime
4. Creating Stored Procedures
   • 4.1 Design-Time
   • 4.2 Runtime
5. Additional Information

Requirements

This tutorial assumes that you have already connected to the server (see Connecting to PostgreSQL). To create database objects at runtime, add the PgAccess and PgScript units to the uses clause for Delphi or include the PgAccess.hpp and PgScript.hpp header files for C++ Builder.

General Information

Database objects are created using Data Definition Language (DDL), which is part of the SQL language. The user must have the appropriate privileges to execute DDL statements on the server. There are two ways to create database objects: build DDL statements manually and execute them with a component like TPgSQL, or use a GUI tool like pgAdmin. This tutorial uses the data access components to create tables and stored procedures.

Creating Tables

To create tables, the TPgSQL component is used in this tutorial.

Design-Time

• Find the TPgSQL component in the PgDAC category on the Tool Palette.
• Double-click the component. A new object will appear on the form. If this is the first TPgSQL object in this project, it will be named PgSQL1. Note that the Connection property is automatically set to an existing connection.
• Double-click the PgSQL1 object.
• Enter the following statements:
  

  CREATE TABLE dept (
    deptno serial not null,
    dname varchar(14),
    loc varchar(13),
    primary key (deptno)
    );
    CREATE TABLE emp (
    empno serial not null,
    ename varchar(10),
    job varchar(9),
    mgr integer,
    hiredate timestamp,
    sal int,
    comm int,
    deptno int references dept,
    primary key (empno)
  );
  

• Click the Execute button to create two tables.

Runtime

The same tables created at runtime:

Delphi

var
  PgSQL1: TPgSQL;
begin
  PgSQL1 := TPgSQL.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgSQL1.Connection := PgConnection1; 
    //adds statements to create tables
    PgSQL1.SQL.Add('CREATE TABLE dept (');
    PgSQL1.SQL.Add('  deptno serial not null,');
    PgSQL1.SQL.Add('  dname varchar(14),');
    PgSQL1.SQL.Add('  loc varchar(13),');
    PgSQL1.SQL.Add('  primary key (deptno)');
    PgSQL1.SQL.Add(');');
    PgSQL1.SQL.Add('CREATE TABLE emp (');
    PgSQL1.SQL.Add('  empno serial not null,');
    PgSQL1.SQL.Add('  ename varchar(10),');
    PgSQL1.SQL.Add('  job varchar(9),');
    PgSQL1.SQL.Add('  mgr integer,');
    PgSQL1.SQL.Add('  hiredate timestamp,');
    PgSQL1.SQL.Add('  sal int,');
    PgSQL1.SQL.Add('  comm int,');
    PgSQL1.SQL.Add('  deptno int references dept (deptno)');    
    PgSQL1.SQL.Add(');');
    // executes the statements
    PgSQL1.Execute;
  finally
    PgSQL1.Free;
  end;
end;

C++Builder

TPgSQL* PgSQL1 = new TPgSQL(NULL);
try {
  // PgConnection1 was set up earlier
  PgSQL1->Connection = PgConnection1; 
  //adds statements to create tables
  PgSQL1->SQL->Add("CREATE TABLE dept (");
  PgSQL1->SQL->Add("  deptno serial not null,");
  PgSQL1->SQL->Add("  dname varchar(14),");
  PgSQL1->SQL->Add("  loc varchar(13),");
  PgSQL1->SQL->Add("  primary key (deptno)");
  PgSQL1->SQL->Add(");");
  PgSQL1->SQL->Add("CREATE TABLE emp (");
  PgSQL1->SQL->Add("  empno serial not null,");
  PgSQL1->SQL->Add("  ename varchar(10),");
  PgSQL1->SQL->Add("  job varchar(9),");
  PgSQL1->SQL->Add("  mgr int,");
  PgSQL1->SQL->Add("  hiredate timestamp,");
  PgSQL1->SQL->Add("  sal int,");
  PgSQL1->SQL->Add("  comm int,");
  PgSQL1->SQL->Add("  deptno int references dept (deptno)");
  PgSQL1->SQL->Add(");");
  // executes the statements
  PgSQL1->Execute();
}
__finally {
  PgSQL1->Free();
}

Creating Stored Procedures

To create stored procedures, the TPgScript component is used in this tutorial.

Design-Time

• Find the TPgScript component in the PgDAC category on the Tool Palette.
• Double-click the component. A new object will appear on the form. If this is the first TPgScript object in this project, it will be named PgScript1. Note that the Connection property is already set to an existing connection.
• Double-click the PgScript1 object.
• Enter the following statement:
  

  CREATE FUNCTION TenMostHighPaidEmployees()
  RETURNS SETOF Emp AS $$
    SELECT * FROM emp ORDER BY emp.sal DESC LIMIT 10;
  $$ LANGUAGE sql;
  
  CREATE FUNCTION GetEmpNumberInDept(
    IN  pdeptno int,
    OUT pempnumb int)
  RETURNS int AS $$
  BEGIN
    pempnumb := (SELECT count(*) FROM emp WHERE deptno = :pdeptno);
  END;
  $$ LANGUAGE plpgsql;
  

• Click the Execute button to create two stored procedures.

Runtime

The same stored procedures created at runtime:

Delphi

var
  PgScript1: TPgScript;
begin
  PgScript1 := TPgScript.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgScript1.Connection := con; 
    //adds statements to create procedures
    PgScript1.SQL.Add('CREATE FUNCTION TenMostHighPaidEmployees()');
    PgScript1.SQL.Add('RETURNS SETOF Emp AS $$');
    PgScript1.SQL.Add('  SELECT * FROM emp ORDER BY emp.sal DESC LIMIT 10');
    PgScript1.SQL.Add('$$ LANGUAGE sql;');
    PgScript1.SQL.Add('');
    PgScript1.SQL.Add('CREATE FUNCTION GetEmpNumberInDept(');
    PgScript1.SQL.Add('  IN  pdeptno int,');
    PgScript1.SQL.Add('  OUT pempnumb int)');
    PgScript1.SQL.Add('RETURNS int AS $$');
    PgScript1.SQL.Add('BEGIN');
    PgScript1.SQL.Add('  pempnumb := (SELECT count(*) FROM emp WHERE deptno = :pdeptno);');
    PgScript1.SQL.Add('END;');
    PgScript1.SQL.Add(' $$ LANGUAGE plpgsql;');
    // executes the statements
    PgScript1.Execute;
  finally
    PgScript1.Free;
  end;
end;

C++Builder

TPgScript* PgScript1 = new TPgScript(NULL);
try {
    // PgConnection1 was set up earlier
    PgScript1->Connection = con;
    //adds statements to create procedures
    PgScript1->SQL->Add("CREATE FUNCTION TenMostHighPaidEmployees()");
    PgScript1->SQL->Add("RETURNS SETOF Emp AS $$");
    PgScript1->SQL->Add("  SELECT * FROM emp ORDER BY emp.sal DESC LIMIT 10");
    PgScript1->SQL->Add("$$ LANGUAGE sql;");
    PgScript1->SQL->Add("");
    PgScript1->SQL->Add("CREATE FUNCTION GetEmpNumberInDept(");
    PgScript1->SQL->Add("  IN  pdeptno int,");
    PgScript1->SQL->Add("  OUT pempnumb int)");
    PgScript1->SQL->Add("RETURNS int AS $$");
    PgScript1->SQL->Add("BEGIN");
    PgScript1->SQL->Add("  pempnumb = (SELECT count(*) FROM emp WHERE deptno = :pdeptno);");
    PgScript1->SQL->Add("END;");
    PgScript1->SQL->Add(" $$ LANGUAGE plpgsql;");
    // executes the statements
    PgScript1->Execute;
}
__finally {
    PgScript1->Free();
}

Additional Information

There are many ways to create database objects on the server. Any tool or component that is capable of running an SQL query can be used to manage database objects. For example, TPgSQL can be used to insert statements one by one, while TPgScript is intended to execute multiple DDL/DML statements as a single SQL script. For more information on DDL statements, refer to the PostgreSQL documentation.