How to refer to computed columns from other computed columns?

Поиск
Список
Период
Сортировка
От Matthew Wilson
Тема How to refer to computed columns from other computed columns?
Дата
Msg-id i4bbti$q0q$1@dough.gmane.org
обсуждение исходный текст
Ответы Re: How to refer to computed columns from other computed columns?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: How to refer to computed columns from other computed columns?  (Eric Ndengang <eric.ndengang_foyet@affinitas.de>)
Список pgsql-general
I'm converting some procedural code to SQL as an experiment.  Here's the
pseudocode:

    c = a - b
    if c < 0 then d = 'no'
    else d = 'yes'

In SQL, I've got this:

    select a, b, a - b as c,
    case when a - b < 0 then 'no'
    else 'yes'
    end as d

    from foo;

This is a trivial example, but you can see how I calculate a - b two
separate times.

In reality, I have much nastier calculations and they happen more than
just twice.

I'm looking for an elegant solution for this puzzle.  I don't want to
repeat that a - b part over and over because I likely will need to
change how c gets defined and I don't want to have to change more than
one place in the code.

All I can come up with so far is to use a view and then another view on
top of that one:

    create view v1 as
    select a, b, a - b as c
    from foo;

    create view v2 as
    select a, b, c,
    case when c < 0 then 'no'
    else 'yes'
    end as d
    from v1;

This is better than the first solution because c is only defined in a
single place.  Is this the best possible solution?

Thanks for the help.

Matt

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian von Bidder
Дата:
Сообщение: Re: good exception handling archiecutre
Следующее
От: Tom Lane
Дата:
Сообщение: Re: How to refer to computed columns from other computed columns?