PgDAC

Creating Database Objects

This tutorial describes how to create tables, stored procedures and other objects on PostgreSQL.

  1. Requirements
  2. General information
  3. Creating tables
  4. Creating Stored Procedures
  5. Additional information

Requirements

In order to create database objects you have to connect to PostgreSQL. This process is described in details in the tutorial "Connecting To PostgreSQL".

General information

Database objects are created using Data Definition Language (DDL), which is a part of SQL. The DDL statements can be executed on server by an account that has the necessary privileges. There are two ways to create database objects. You can build DDL statements manually and execute them using a component like TPgSQL. Another way is to use visual database tools like pgAdmin. This topic covers the first way - using components.
There are two ways to execute DDL statements in components like TPgSQL: in design-time and in run-time. Both these ways are described below.

Note: The following assumes that you have the IDE running, you are currently focused on the form designer, and you have already set up the TPgConnection component on the form.

Creating tables

To create tables, the TPgSQL component is used here.

Design-time creation

Run-time creation

Same operations performed in runtime look as follows:

[Delphi]

var
  sql: TPgSQL;
begin
  sql := TPgSQL.Create(nil);
  try
    sql.Connection := con; // con is TPgConnection already set up
    sql.SQL.Clear;
    sql.SQL.Add('CREATE TABLE dept (');
    sql.SQL.Add('  deptno serial not null,');
    sql.SQL.Add('  dname varchar(14),');
    sql.SQL.Add('  loc varchar(13),');
    sql.SQL.Add('  primary key (deptno)');
    sql.SQL.Add(');');
    sql.SQL.Add('CREATE TABLE emp (');
    sql.SQL.Add('  empno serial not null,');
    sql.SQL.Add('  ename varchar(10),');
    sql.SQL.Add('  job varchar(9),');
    sql.SQL.Add('  mgr integer,');
    sql.SQL.Add('  hiredate timestamp,');
    sql.SQL.Add('  sal real,');
    sql.SQL.Add('  comm real,');
    sql.SQL.Add('  deptno int references dept,');
    sql.SQL.Add('  primary key (empno)');
    sql.SQL.Add(');');
    sql.Execute;
  finally
    sql.Free;
  end;
end;

[C++Builder]

{
  TPgSQL* sql = new TPgSQL(NULL);
  try
  {
	sql->Connection = con; // con is TPgConnection already set up
	sql->SQL->Clear();
	sql->SQL->Add("CREATE TABLE dept (");
	sql->SQL->Add("  deptno serial not null,");
	sql->SQL->Add("  dname varchar(14),");
	sql->SQL->Add("  loc varchar(13),");
	sql->SQL->Add("  primary key (deptno)");
	sql->SQL->Add(");");
	sql->SQL->Add("CREATE TABLE emp (");
	sql->SQL->Add("  empno serial not null,");
	sql->SQL->Add("  ename varchar(10),");
	sql->SQL->Add("  job varchar(9),");
	sql->SQL->Add("  mgr integer,");
	sql->SQL->Add("  hiredate timestamp,");
	sql->SQL->Add("  sal real,");
	sql->SQL->Add("  comm real,");
	sql->SQL->Add("  deptno int references dept,");
	sql->SQL->Add("  primary key (empno)");
	sql->SQL->Add(");");
	sql->Execute();
  }
  __finally
  {
	sql->Free();
  }
}

Creating Stored Procedures

To create tables, the TPgScript component is used here.

Design-time creation

Run-time creation

The same operations performed in runtime look as follows:

[Delphi]

var
  script: TPgScript;
begin
  script := TPgScript.Create(nil);
  try
    script.Connection := con; // con is TPgConnection already set up
    script.SQL.Clear;
    script.SQL.Add('CREATE FUNCTION TenMostHighPaidEmployees()');
    script.SQL.Add('RETURNS SETOF Emp AS $$');
    script.SQL.Add('  SELECT * FROM emp ORDER BY emp.sal DESC LIMIT 10');
    script.SQL.Add('$$ LANGUAGE ''sql'';');
    script.SQL.Add('');
    script.SQL.Add('CREATE FUNCTION GetEmpNumberInDept(');
    script.SQL.Add('  IN  pdeptno integer,');
    script.SQL.Add('  OUT pempnumb integer)');
    script.SQL.Add('RETURNS integer AS');
    script.SQL.Add('$BODY$');
    script.SQL.Add('BEGIN');
    script.SQL.Add('  pempnumb := (SELECT count(*) FROM emp WHERE deptno = pdeptno);');
    script.SQL.Add('END;');
    script.SQL.Add('$BODY$');
    script.SQL.Add('  LANGUAGE plpgsql VOLATILE');
    script.Execute;
  finally
    script.Free;
  end;
end;

Note: To run this code, you have to add the PgScript unit to the USES clause of your unit.

[C++Builder]


TPgScript* script = new TPgScript(NULL);
try {
    script->Connection = con; // con is TPgConnection already set up
    script->SQL.Clear;
    script->SQL->Add("CREATE FUNCTION TenMostHighPaidEmployees()");
    script->SQL->Add("RETURNS SETOF Emp AS $$");
    script->SQL->Add("  SELECT * FROM emp ORDER BY emp.sal DESC LIMIT 10");
    script->SQL->Add("$$ LANGUAGE ''sql'';");
    script->SQL->Add("");
    script->SQL->Add("CREATE FUNCTION GetEmpNumberInDept(");
    script->SQL->Add("  IN  pdeptno integer,");
    script->SQL->Add("  OUT pempnumb integer)");
    script->SQL->Add("RETURNS integer AS");
    script->SQL->Add("$BODY$");
    script->SQL->Add("BEGIN");
    script->SQL->Add("  pempnumb = (SELECT count(*) FROM emp WHERE deptno = pdeptno);");
    script->SQL->Add("END;");
    script->SQL->Add("$BODY$");
    script->SQL->Add("  LANGUAGE plpgsql VOLATILE");
    script->Execute;
}
__finally {
    script->Free();
}

Note: To run this code, you have to include the PgScript.hpp header file to your unit.

Additional information

Actually, there are lots of ways to create database objects on server. Any tool or component that is capable of running an SQL query, can be used to manage database objects. For example, TPgSQL suits fine for creating objects one by one, while TPgScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to the PostgreSQL documentation.

© 1997-2020 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback