Re: expr ? trueval : falseval - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: expr ? trueval : falseval
Date
Msg-id 35141C00.358B066E@sid.trust.ee
Whole thread Raw
In response to expr ? trueval : falseval  (Brett McCormick <brett@work.chicken.org>)
List pgsql-hackers
Brett McCormick <brett@work.chicken.org> wrote:

> expr ? trueval : falseval
>
> if expr evaluated is true, the entire expression is trueval evaluated,
> otherwise it is falseval evaluated.
>
> Is it possible to have this sort of grammar logic in the parser?

For achieving this kind of functionality, you can also define a
function like Oracle does. Actually the Oracle way is more general.

This function (i can't remember its name, but lets assume it is
called CHOICE) behaves as follows:

it has as its first argument a testvalue followed by pairs of arguments,
match and value, and possibly a default value as last argument.

the function is evaluated so that a value whose match equals
testvalue is returned.

so for your case you would call:

CHOICE(expr,'T',trueval,'F',falseval)

but it is much more general, for example for getting a nice table of
amounts of something bought quarterly, you do the following.

select
  name as "Name",
  sum(choice(quarter(buy_time),'1',amount,0)) as "Q1",
  sum(choice(quarter(buy_time),'2',amount,0)) as "Q2",
  sum(choice(quarter(buy_time),'3',amount,0)) as "Q3",
  sum(choice(quarter(buy_time),'4',amount,0)) as "Q4",
  sum(amount) as "Year total"
from
  buyings
group by name;

and get the following

Name   | Q1  | Q2  | Q3  | Q4  | Total
-------+-----+-----+-----+-----+--------
cats   |   0 |   0 |   3 |   7 |   10
dogs   |   1 |   2 |   3 |   4 |   10
ducks  |   1 |   1 |   1 |   1 |    4


What holds us back from defining a function like this now is
difficulty of defining functions that can accept arguments and
can return values of an opaque type (or actually type defined
at invocation time)

Or actually there is (or at least was a little while back) a function
COUNT that can take any type of argument and return an integer, so
_this_ should be possible to define.

But I have not found any reference how to tell a function that it has
variable number of arguments and that it can itself tell what it returns
based on what arguments are given. I think that this is quite hard given
the current implementation.

Hannu

pgsql-hackers by date:

Previous
From: Maurice Gittens
Date:
Subject: patch for memory overrun on Linux(i386)
Next
From: The Hermit Hacker
Date:
Subject: Re: [HACKERS] patch for memory overrun on Linux(i386)