PgMDD: How to RETURNS TABLE

Our client asked:

I have a script that can be created directly from PgAdmin, but it is not possible to create these scripts in PgMDD.

The function has a simple structure as defined below. The important part, is the return type (TABLE)

CREATE FUNCTION function_name() RETURNS TABLE(column_name_1 integer, column_name_2 character varying)
AS
BEGIN
RETURN QUERY (SELECT id, name FROM customer);
END;

PgMDD does not allow you to to that.

Yeap, indeed PgMDD doesn’t support ANSI syntax RETURNS TABLE, but you may do this in PostgreSQL way by using OUT parameters.

There is a good article “Using RETURNS TABLE vs. OUT parameters” by Leo Hsu and Regina Obe describing this approach.

So this function may be defined like this using PgMDD:

CREATE FUNCTION function_name(
  OUT column_name_1 integer,  
  OUT column_name_2 character varyingRETURNS SETOF RECORD
AS
$BODY$
   RETURN QUERY (SELECT id, name FROM customer);
$BODY$;

PaGoDump: release & trick

Release

First of all my congratulations to the whole advanced mankind – PaGoDump v1.0.2 is out!

For people in the tank PaGoDump v1.0.2 is a free GUI Windows utility for backing up a PostgreSQL database with Unicode support.

Trick

Now that PaGoDump takes command-line parameters and pgAdmin III v1.10 supports plugins as well, I rushed to take advantage of this:

  1. Open %pgAdmin%\plugins.ini configuration file in your favourite text editor
  2. Add these lines:
;
; PaGoDump (Windows):
;
[Separator]
Title=PaGoDump
Command=”C:\PaGoDump\PaGoDump.exe” –host=”$$HOSTNAME” –port=$$PORT –user=”$$USERNAME” –db=”$$DATABASE” –pwd=”$$PASSWORD”
Description=Perform a backup of the current database.
KeyFile=C:\PaGoDump\PaGoDump.exe
Platform=windows
ServerType=postgresql
Database=Yes
SetPassword=No

Pay attention that Command option is the one long line.

PaGoDump as pgAdmin plugin

PaGoDump as pgAdmin plugin

Take care!

PostgresDAC-2.5.1: Parameters support in TPSQLDirectQuery

Preface

Once again I am to reveal PostgresDAC-2.5.1 “yummies”. Who missed the beginning could easily catch up.

Crux of the matter

TPSQLDirectQuery allows to execute SQL queries and retrieve result sets very efficiently, it provides great performance actually. Meanwhile, it is not TDataset-compatible. This means that it can’t be assigned to TDatasource.Dataset property and you can’t use it with visual DB-controls. TPSQLDirectQuery is usually used in tasks where data requires some processing without displaying it with/within visual DB-controls.

That’s why it is very simple. But we decided that simplicity is not the antonym for flexibility, so we added the parameters support.

A parameterized SQL statement contains parameters or variables. Parameters can replace data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement.

SELECT * FROM "Customers" WHERE "CustID" = :CustParam;
SELECT * FROM "Customers" WHERE "CustID" = ?;

Note that the names of parameters in usual Delphi notation begin with a colon. The colon is required so that the parameter names can be distinguished from literal values. You can also include unnamed parameters by adding a question mark (?) to your query. Unnamed parameters are identified by position, because they do not have unique names.

But instead of usual Delphi parameters representation we decided to use PostgreSQL one:

SELECT * FROM "Customers" WHERE "CustID" = $1;

Why we preferred PostgreSQL syntax over standard Delphi one? Because SQL with parameters will be sent to server without changes, and parameters’ values will be sent separately. But if we use Delphi parameters then client must replace them before sending, that leads us to tedious and error-prone quoting and escaping.

One more thing. So, not to trouble ourselves we didn’t parse SQL for automatic parameters creation. This means that developer should manually create needed parameters.

PSQLDirectQuery1.SQL.Text := 'SELECT * FROM Customer WHERE CustNo > $1';
with PSQLDirectQuery1.Params.CreateParam(ftInteger, '', ptOutput) do 
  Value := 5132;
PSQLDirectQuery1.Open;
ShowMessage(Format('There is %d rows', [PSQLDirectQuery1.RecordCount]));

Stay tuned! 😉