2

I'd like to know if there is a possibility to create something like named calculations in Postgres? Say, I have a table:

create table foo (
  bar smallint,
  baz smallint
)

I can run select (bar / baz) * 100 from foo; to get my result, but I'd like to have a formula (pseudocode): avg_foo ::= (bar/baz)*100 and then do select avg_foo from foo; to get the same result. Ideally, I'd like to have a separate table with calculations:

create table calculations (
  name varchar,
  formula varchar
)

So that I could create calculations dynamically and use them in selects, like this:

insert into calculations (name, formula) values
  ('sum_bar_baz', 'bar+baz'),
  ('mult_bar_baz', 'bar*baz');
select sum_bar_baz, mult_bar_baz from foo;

How do I do this with Postgres?

Alex Tokarev
  • 4,821
  • 1
  • 20
  • 30

1 Answers1

0

Write a tuple returning function in plpgsql, and call the required functions from within.

Daniel
  • 27,718
  • 20
  • 89
  • 133
  • 1
    Daniel, thanks for the answer. Could you be a bit more specific? I'm not sure how to do what you said. – Alex Tokarev Mar 15 '11 at 20:07
  • Learn how to write functions in postgres. Learn how to write plpgsql functions. Read about the EXECUTE statement. It takes a string, parses it, and evaluates it. Use it to create a dynamic query. Learn how to return tuples instead of single values. Read the docs. – Daniel Mar 15 '11 at 22:32