Tutorial 6 - Create C function using wizard

Step 0 - Coding in C, start Wizard

This tutorial show, how to create new function 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. 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 function in C language

In this tutorial we create function in C language. This function return sum from two numbers. It is simple and absolutely useless. It's only sample. Here is function source - sample.c:

#include "postgres.h"
#include "funcapi.h"
#include "catalog/pg_type.h"

PG_FUNCTION_INFO_V1(float8add);

Datum float8add(PG_FUNCTION_ARGS) 
{ 
	float8 arg1 = PG_GETARG_FLOAT8(0); 
	float8 arg2 = PG_GETARG_FLOAT8(1); 
	
	float8 result; 
	result = arg1 + arg2; 
	
//	CheckFloat8Val(result); 
	PG_RETURN_FLOAT8(result); 
}

Also you need Makefile for this function:

PG_INCLUDE = '/usr/include/pgsql/server'
PG_LIBDIR = `pg_config --libdir`
PG_PACKAGE = `pg_config --pkglibdir`

all:
	gcc -fpic -I$(PG_INCLUDE) sample.c -c -o sample.o
	gcc -shared -o sample.so sample.o
	cp sample.so $(PG_PACKAGE)/sample.so

Create those two files 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.

Start wizard

In left tree view, select Database or Schema and choose menu command Database -> Wizard. Select function icon from displayied dialog.

Or select Functions folder and make right button click on it to display object menu.

KPoGre displays wizard for create new function. This wizard have 7 steps.

Step 1 - Function name

If you start this wizard when Database is selected, choose schema for new function. When you run wizard from Schema or Tables folder, actual schema is used.

Type name for new function. Select language.

Press Next button.

Step 2 - Return and argument types

Choose Return type. You may choose any basic data type, or OPAQUE or any composite type. In this sample, we choose double precision type.

Choose function Arguments. In this sample, we add two double precision arguments.

Press Next button.

Step 3 - Function properties

In this step, choose function properties.

Type

These attributes inform the system whether it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. Volatile is the default assumption.

• Immutable indicates that the function always returns the same result when given the same argument values.
• Stable indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements.
• Volatile indicates that the function value can change even within a single table scan, so no optimizations can be made.

NULL arguments behavior

• Called on NULL input (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately.
• Strict indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

Security

• Security invoker indicates that the function is to be executed with the privileges of the user that calls it. That is the default.
• Security definer specifies that the function is to be executed with the privileges of the user that created it. Note that you should be careful when creating function with SECURITY DEFINER option. This is potentially security hole.

After defining all properties press Next button.

Step 4 - Function definition

Enter Function library name - sample.so. Also specify function name inside library - float8add

Step 5 - Grant execute privileges

This step allow grant EXECUTE privilege on function to one or more users or groups of users. These privileges are added to those already granted, if any.

Select users or groups (multiple select allowed) or check PUBLIC for grant to all users.

Step 6 - Review SQL statement

In this step, SQL statement for create function 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 7 - Check operation result

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

Press Finish button and test newly created function.