Writing complex procedures to return data in PgSql

March 12th, 2007

Postgresql is an extremely powerful open-source RDBMS. In fact, I use it on a day-to-day basis for my work and I absolutely love it. There are some quirks, the speed is a little slow (especially when compared to it’s big competitor – MySQL), but overall it implements the ISO standards for RDBMS’s better than almost any database out there.

Having said that, though, the biggest fault with PostGres is the lack of easy documentation. For most major products, all you need to do is Google for your requirement, and someone, somewhere would have found the answer and put it online.

But with PostGres, you generally are taken back to the (admittedly extensive) manual pages. But they still don’t have enough examples. So you don’t know what will work and what won’t without a lot of trial and error.

Take this case (not the actual problem I was working on, but a pale equivalent):

You have a bunch of books. You know the authors and the books and the subjects they have written. Now you want a list per author of the number of books in each subject.

Yes, this example is a little lame, because you can do it with a simple lookup and a view. BUT, for the sake of trying to give an answer to a question that others may be seeking let’s look at this function.

CREATE OR REPLACE FUNCTION author_subject_list() RETURNS SETOF RECORD AS $$
DECLARE
SUBLIST RECORD ;
AUTHLIST RECORD ;
QUERYST TEXT ;
BEGIN
QUERYST:=’SELECT sum(SUBJ_A),sum(SUBJ_B) from AUTH_SUBJ where AUTH=’;
FOR AUTHLIST IN
SELECT AUTH FROM AUTH_LIST ORDER BY AUTH
LOOP
QUERYST:=QUERYST || ”” || AUTHLIST.AUTH || “””” ;
EXECUTE QUERYST INTO SUBLIST ;
RETURN NEXT SUBLIST ;
END LOOP ;
RETURN ;
END ;
$$
LANGUAGE ‘plpgsql’ VOLATILE;

It looks very simple but it’s not. And to top it off, when you query on this function you have to use a syntax like

SELECT * FROM author_subject_list() as (sum_subj_a as bigint, sum_subj_b as bigint);

otherwise it will give you an error.

Note that in the function I have given 4 ‘ marks in the line QUERYST:=… Prior to PgSQL 8.2 you would have escaped the ‘ marks by giving a \’.

Also remember to give the return next as the first return line (within the loop) and a plain return outside the loop.

There are easier ways to solve this particular problem, but the problem that I was trying to solve was much more complex. If you need to do something similar, please feel free to use this as a guide, or just contact me.

Categories: Database | Tags: , , , , , | No Comments