plpgsql – Page 3 – select * from depesz;

Getting session variables without touching postgresql.conf

This post has been updated with new code that uses temporary table – the code is at the end of post!

There was this question on Stack Overflow.

For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying postgresql.conf – so usage of custom_variable_classes is forbidden 🙂

While I don't actually see why somebody would want to avoid modifying its postgresql.conf (short of “it's shared hosting and I don't have superuser privileges"), I thought that it will be rather simple, and at the same time, rather interesting.

So, let's do it:

Continue reading Getting session variables without touching postgresql.conf

Calculating median

Today, on irc (#postgresql on freenode.net) Dim mentioned about writing median calculation code.

It got me thinking, and consequently writing my version of median calculation code.

Continue reading Calculating median

Getting list of unique elements

Every so often you need to get list of unique elements in some column. The standard way to do it is:

SELECT DISTINCT COLUMN FROM TABLE;

SELECT COLUMN FROM TABLE GROUP BY COLUMN;

The only problem is that it's slow – as it has to seq scan whole table. Can it be done faster?

Continue reading Getting list of unique elements

Getting list of all children in “adjacency list” tree structure

So, you have a table which looks like this:

# \d test
                           Table "public.test"
  Column   |  Type   |                     Modifiers
-----------+---------+---------------------------------------------------
 id        | integer | not null default nextval('test_id_seq'::regclass)
 parent_id | integer |
 x         | text    |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "test_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES test(id)
Referenced by:
  "test_parent_id_fkey" IN test FOREIGN KEY (parent_id) REFERENCES test(id)

And you would like to easily get all children starting from given node?

Continue reading Getting list of all children in “adjacency list" tree structure

Getting list of most common domains

Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:

I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).

Continue reading Getting list of most common domains