Tutorial 6 - Create base type using wizard

Step 0 - Coding in C, registering functions

This tutorial show, how to create base type using wizard in KPoGre. Wizard is not able to handle all possible requirements, but it is suitable for most of common situations. I expect, that you are basicaly familiar with CREATE FUNCTION statement and with CREATE TYPE statement. Also you must have good C language knowledge to write your own functions. You may start here to get informations about PostgreSQL C functions specifics.

Coding datatype and functions in C language

In this tutorial we create base datatype named complex. This datatype represent complex number. Before registering type, we need two or more functions - input_function and output_function are required, while the functions receive_function, send_function and analyze_function are optional. Generally these functions have to be coded in C. We don't create these function from scratch, but use this sample instead of it.

Download source code and run make -f Makefile from shell in directory (with root privileges), where files stored. Of course, this step must be done on machine, where PostgreSQL server is installed.

Registering functions - fast way

Now, we have created library with functions. We need register these functions. We may use wizards for each function or use prepared SQL script. The fast way is here:

CREATE FUNCTION complex_in(cstring)
    RETURNS complex
    AS 'complex.so' , 'complex_in'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_out(complex)
    RETURNS cstring
    AS 'complex.so' , 'complex_out'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_recv(internal)
   RETURNS complex
   AS 'complex.so' , 'complex_recv'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_send(complex)
   RETURNS bytea
   AS 'complex.so' , 'complex_send'
   LANGUAGE C IMMUTABLE STRICT;

Connect to database and run this script. Then refresh functions folder:

Registering functions - using wizards

Of course you may use wizards for create these functions.

Note, that datatype complex is not exists at this time. But we want to create functions, that uses this datatype as argument or return type. To solve thit situation - use button User defined datatype and type complex to input dialog. After this, our datatype can be selected in datatype comoboxes.

In last wizard page you may see notice, that PostgreSQL create a "shell" type, which is simply a placeholder entry in the system catalog, and link the input function definition to the shell type. Similarly the other functions will be linked to the (now already existing) shell type. Finally, CREATE TYPE replaces the shell entry with a complete type definition, and the new type can be used.

Repeat these steps and create all needed functions - input_function, output_function, receive_function, send_function.

Looking at shell datatype

If you interested, you may see complex datatype without functions and with no owner.

Step 1 - Type name

Now, start create base datatype wizard - select types folder and press right mouse button. Then choose menu item for this action.

Type name for new datatype. Of course, name must be complex.

Press Next button.

Step 2 - Define functions

Choose functions in comboboxes. Listed are only functions with proper parameters and return type.

Press Next button.

Step 3 - Define type properties

In this step, choose type properties.

Internal length - a numeric constant that specifies the length in bytes of the new type's internal representation. The default assumption is that it is variable-length.

Alignment - The storage alignment requirement of the data type. If specified, it must be char, int2, int4, or double; the default is int4.

Storage - The storage strategy for the data type. If specified, must be plain, external, extended, or main; the default is plain.

Default value - The default value for the data type. If this is omitted, the default is null.

Element - The type being created is an array; this specifies the type of the array elements.

Delimiter - The delimiter character to be used between values in arrays made of this type.

After defining all properties press Next button.

Step 4 - Review SQL statement

In this step, SQL statement for create type is displayied for your check. You may modify it, if you understand, what do you do.

When you press Next button command will be send to server.

Step 5 - Check operation result

Finally, check operation result. If you get any error, you may go Back and try make changes and retry step 4.

Press Finish button and see newly created datatype. Also you may see that PostgreSQL automatically creates an associated array type _complex, whose name consists of the base type's name prepended with an underscore.

Step 6 - Using base type

You can use base type as column when create table, or as base type for domain or as return or argument type for function.