Gevik's PostgreSQL Blog

This maybe trivial but I think it is worth sharing anyway
Consider using LIMIT/OFFSET in a function where as parameter you have to specify values for both.
In the following function both limit and offset parameters are integers. This will cause an error when you want to pass ALL (for LIMIT ALL) as argument.


PostgreSQL function:

create or replace function sp_category_getlist(plimit integer,poffset integer) returns setof category as
$$
	select 
		*
	from
		category
        order by 
		caption
	limit 
		$1 offset $2
$$
language sql;

Fortunately PostgreSQL accepts NULL function arguments which in this case is treated as ALL

Example in PHP:

/* sp_filesystem_category_getlist */
public function sp_filesystem_category_getlist($puserid,$plimit=null,$poffset='0')
{
	$args = array($plimit,$poffset);
        $sql = "select * from sp_filesystem_category_getlist($1,$2)";
        $result=$this->query($sql,$args);
        return pg_fetch_all($result);
}

One of the cool new features in PostgreSQL in the connect privilege. The CONNECT privilege enables you to grant connection rights from a user to a database, avoiding the need to configure user to database access in the pg_hba.conf.

The information in the pg_hba.conf could exclusively be used to administer the authentication (Are you allowed on my server?) process and the CONNECT privilege could be used for authorization (What are you allowed to do on my server?)

Let us look at the following scenario:

Imagine you want to enable different users on your network to access different databases on your server. Configuring every user for every permitted database in pg_hba.conf would be time consuming and less administer-friendly. The process could be simplified by allowing a certain network (or subnet) to authenticate and handle the database connection by CONNECT privilege.

In pg_hba.conf:
# Allow all network users to connect to by server.
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 192.168.1.0/24 password

Then secure the databases by:

-- Revoke connection right from all users to main database.
-- Dont worry, the superuser can still connect.
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

-- create a new user called joe
CREATE ROLE joe LOGIN;

-- create a new database owned by joe.
CREATE DATABASE customers OWNER joe ENCODING UTF8

-- Revoke connection from everybody on customers database
REVOKE CONNECT ON DATABASE customers FROM PUBLIC;

-- Authorize Joe exclusively to connect to customers database
GRANT CONNECT ON DATABASE customers TO joe;

This way only Joe or anyone in that role can connect customers database.

It is almost time.... Among other PG related things ,I am also working on the PostgreSQL Dutch community website. The website is meant to promote PostgreSQL in The Netherlands and be a central point to answer PG related questions for the dutch community.

It's been a long time since I have posted. Because of a broken wrist I was out for sometime. Slowly but surely I am crawling back to the things I left unfinished before I managed to fall and break my wrist.

In the coming weeks I am going to finish the sound editing from the "PostgreSQL Summit" and if everything goes okay I will start working on the GUID datatype.

The last couple of days I have been annoying some people in order to get insight about some inner workings of PostgreSQL. Alvaro Herrera has accepted to coach me through understanding the code and of course I also get valuable information from Tom Lane. I extremely am grateful and happy about this because I get to learn PostgreSQL inside out and hopefully I will be able to contribute to the project by developing some of the TODO items.

Programming with PostgreSQL gets more fun every day especially when combining it with .NET Framework. As I was working on my PostgreSQL Code Template Wizard, I thought why not add support for creating code templates for Stored Procedures. The result of this experimentation is (as you might have guessed) The Stored Procedure Wizard. With this tool one is able to create code templates with StringTemplate (www.stringtemplate.org) to generate code for stored procedures from PostgreSQL in any programming language.

See some screenshots.

This wizard is the result of my experimentations with PostgreSQL catalog tables and schema information. For some of PostgresSQL projects I am currently working on, I needed a simple yet very powerful template generator. I browsed the net for code generators and I must say that there are a lot of those out there, but to my experience none provided the amount of extensibility and diversity I require.

My requirements where simple: I needed a code template generator tightened with PostgreSQL in order to generate all kinds of code (PgPL/SQL, C#, ASP.NET, PHP). The template creation process and the “template language” had to be very straightforward and easy. With other tools I found on the net, I would have spent more time to actually create a template than developing the required code by hand. The application had to be very much extensible in order to make adhoc changes.

In order to build this utility I have used the .NET 2.0, npgsql, ANTLR and StringTemplate (Most respect and thanks to Terence Parr), some custom built .NET libraries and of course the two famous Ps (pizza and patience) to make the magic work.

The wizard is still in development. Based on the need, I develop as I go. Here are some screenshots. If you are interested drop me a line. Perhaps I could ask the guys at pgfoundry to create a project home for this if there are enough people interested.

screenshots

As a developer I often come across SQL statements that are hardwired into the source code regardless of the programming language they are used in. It makes the code messy and unreadable when sometimes seeing large SQL statements chopped and concatenated in several string constants.

The information here may be trivial but I would like to share it anyway

PostgreSQL provides us the wonderful feature of “stored procedures” and “user defined functions”. These can abstract and hide the SQL plumbing back into the database leaving your source code much cleaner and readable.


Here is a simple example:

It is always a good practice to avoid code like this.


// You wonder what the heck this SQL does?
$sql = "select e.*";
$sql .= "from employee e inner join employee_department ed on e.empid = ed.empid ";
$sql .= "inner join department d on d.depid = ed.depid where d.manager=$1";
$result = $database->executeQuery($sql,$department);


Instead one could do the same like:

First create a stored procedure called get_employee_list_by_department like:

create or replace function get_employee_list_by_department(integer) returns
	setof employee as
$$
   select
           e.*
   from
            employee e inner join employee_department ed
           on e.empid = ed.empid
           inner join department d
           on d.depid = ed.depid
  where
           d.manager = $1;
$$ language SQL;

Then call the stored procedure from your code like:

$result = $database->executeQuery("get_employee_list_by_department",$department);

This is much cleaner and better don't you think? Also when your data structure changes you only have to change what is in the stored procedure not searching your code anymore.

It has been a while since my last post. I have been very busy with all kinds on things.
The last couple of weeks I have been working on Kennisgres along with the Dutch PostgreSQL website.

The Dutch PostgreSQL website will be up and running soon. We have been very lucky to get web hosting donated. This is exciting because the Dutch community website is going to run PostgreSQL on Windows 2003. I am waiting for some case studies from a couple of Dutch companies which I have contacted.

Developing Kennisgres has been slowed down because of the above. I will continue my work on Kennisgres after postgresql.nl is made public.

Have fun.