learning PostgreSQL – being a n00b once again feels strange

It’s been a while since I’ve been a n00bie*. I’ve been coding for a long time now, but most of it has been in technologies that have evolved from what I knew before – Delphi to Java to c# and .NET, then to Roby, Python and PHP (ok, that was a step backwards, but it looks a lot like c# and Java). Paradox to Interbase to SQL Server. ASP to ASP.NET to anything with an MVC in it. It’s been a nice progression over 10 years (yes, scary, it’s been 10 years or more!).

Until now.

This week, and for the next few weeks/months, I’m porting a fairly major email archiving application over to PostgreSQL. It was going to be going to MySQL, but their licensing isn’t going to work – we can’t use GPL code, we have a product, and we can’t force people to pay 500€, they may as well buy MS SQL! On top of that, the products lacks a lot of the things we need, eg uuid columns and, ya know, stability. Core database tables which are not transaction-safe will never make it into production. The app is currently backed on MS SQL Server, which is performing fine in everything except price.

On the surface, pgsql (as it appears to be known) is very similar to MSSQL – tho it’s actually closer to Oracle. It has all the usual database things – tables, rows, types, keys, indexes, stored procedures/functions etc, and a whole host more that SQL Server doesn’t, like GIS datatypes.

It took me a weekend to write/adapt a tool to port the structure over from mssql to postgres. I have a few tweeks to make – eg identity columns in mssql are INT IDENTITY(1,1) NOT NULL, and in pgsql they are SERIAL (it’s own datatype), but all up, the structure was easy to move over.

The stored procedures, on the other hand, are something else. We don’t have a lot of them, maybe 25 or 30, but some of them are pretty complex.

The basic ones are easy:

create procedure foo(@storeid integer) AS
BEGIN
select messages.identifier, messages.storeid from messages where storeid = @storeid;
END
GO;

becomes

create or replace function foo(in_storeid integer) returns table (identifier integer, storeid integer) as $$
select messages.identifier, messages.storeid from messages where storeid = in_storeid;
$$ language sql;

But SQL Server has one often-used trick that pgsql doesn’t. (note, at this point: if you know otherwise, please, PLEASE, email me!). And that is returning a resultset from a stored procedure.

Now, a few people will scream that you can. And yes, you can. You do it in SQL Server by doing this:

create procedure myproc as
select * from table;

and the result comes back as all columns and rows from table. In pgsql, I need to do something like this:

create function myproc() returns table(a int, b varchar, c text) as $$
select a,b,c from table;
$$ language sql;

I can also return a refcursor, which is a reference to a cursor, tho I’m yet to get that working in the sql tools. The main thing is, I have specify the output columns. This is all well and good for strong typing, or for a few unchanging columns, but I have cases where my resulting sql looks more like this:

select tablea.id, tableb.* from tablea, tableb etc;

So if the structure of tableb changes, and at present it has about 10 columns, I have to recode the function. Queue maintenance nightmare!

Now, I can say

returns setof ….

but that appears to only work in some cases, and I still need to specify the format of the output. Same with

returns table( fields….)

Frankly, it’s doing my head in. Maybe I just need to accept that this is the way of pgsql, and get on with it. But I keep looking for the “better” way to do it, and not finding it.

Which brings me back to the n00bie feeling. In effect, I’m learning pgsql – and pl/pgsql (their dialect of oracle’s pl/sql) – using the equivalent of SQL Server Books Online. But without the index. Or a SQL Guru to help.

Doing that with SQL Server would be suicide, or a recipe for extreme frustration. Postgres isn’t much different, and my brain is fried trying to do it. I’ve ordered a book, which should be here tomorrow, and I’m going to jump on their mailing lists shortly, but still – it’s not nice. I don’t know of many – if any – people who use this in anger, so sadly there isn’t anyone to ask “am I just going down a rat hole here?”.

So now I know how it feels for someone learning SQL Server from scratch – a platform which is SO familiar to me I don’t even think about it. All this DailyWTF quips, funny as they are, take on a new meaning for me at the moment.

* for those who dont know what a n00bie is, it’s someone who is new to something – a mashup of newbie – new person.

Listening to: Sun Control Species – Scienza Nouva. And it’s REALLY good.