pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: nik@iii.co.uk
To: pgsql-sql@postgresql.org
Subject: 'if' decision making in an SQL statement
Date: Mon, 10 Aug 1998 11:23:11 +0100
Message-ID: <19980810112311.C3592@iii.co.uk> (raw)

Hi,

After searching the documentation and mailing list archives I can't find
anything about this -- I'm pretty certain it can't be done, but I 
thought the mailing list might be able to help.

Is there anyway to perform IF...THEN...ELSE logic in a PostgreSQL query?

The specific problem I'm trying to solve is as follows. Given the class

    create table People (
	ID		int default nextvalue('seqPeople'),
	Firstname	text not null,
	Middlenames	text,
	Lastname	text not null
    );

    insert into People (Firstname, Middlenames, Lastname)
        values ('Nik', 'John', 'Clayton');

    insert into People (Firstname, Middlenames, Lastname)
        values ('Helen', 'Jane', 'Baker');

    insert into People (Firstname, Lastname)
        values ('Justin', 'Otto');

    ...

I want to create a query (which will be used as the basis for a view) that 
merges the Firstname, Middlenames and Lastname columns together into one 
column, with the correct spacing.

In Oracle, I'd use the decode() function for this, and write

    select ID, decode(Middlenames, null, Firstname || ' ' || Lastname,
                      Firstname || ' ' || Middlenames || ' ' || Lastname)
               as Fullname
      from People;
 
which expresses the logic

    IF Middlenames IS NULL THEN
        Firstname || ' ' || Lastname
    ELSE
        Firstname || ' ' || Middlenames || ' ' || Lastname
    END IF

and would give

   ID   Fullname
   -------------
   1    Nik John Clayton
   2    Helen Jane Baker
   3    Justin Otto
   ...

I can't see a way of doing this in PostgreSQL, and I don't really want
to have to do this sort of processing in the front end (since I have
several front ends, and putting the same logic in each one strikes me as
foolish when that logic really belongs in the database).

Many thanks for any suggestions.

N
-- 
"So it does!" said Pooh. "It goes in!"
"So it does!" said Piglet. "And it comes out!"
"Doesn't it?" said Eyeore. "It goes in and out like anything."



Message-ID: <19980810112311.C3592@iii.co.uk>
Permalink:  ../19980810112311.C3592@iii.co.uk/
Also on:    postgresql.org/message-id/19980810112311.C3592@iii.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: nik@iii.co.uk
  Subject: Re: 'if' decision making in an SQL statement
  In-Reply-To: <19980810112311.C3592@iii.co.uk>
  References: <19980810112311.C3592@iii.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox