sql - Pass table name used in FROM to function automatically? -

working postgresql 9.6.3. new functions in databases.

let's there multiple tables of item numbers. each 1 has item number, item cost , several other columns factored "additional cost". put calculation function can call of these tables.

so instead of:

select      itemnumber,      itemname,      base,      case          when labor < 100 , overhead < .20         when .....         when .....         when .....         .....     end add_cost,     gpm items1; 

i can do:

select      itemnumber,      itemname,      base,      calc_add_cost(),     gpm items1; 

if want able use on of item tables, guess need set table_name parameter function takes since adding table name function undesirable least.

calc_add_cost(items1) 

however, there simpler way such when call calc_add_cost() use table name from clause?

select ....., calc_add_cost(item1) item1 

just seems redundant.

i did come across few topics titles sounded addressed hoping accomplish, upon reviewing them looked different issue.

you can emulate "computed field" or "generated column" had in mind. basics here:

• store common query column?

simple demo one table:

create or replace function add_cost(items1)  -- function name = default col name   returns numeric $func$ select      case          when $1.labor < 100 , $1.overhead < .20 numeric '1' --      when ..... --      when ..... --      when .....         else numeric '0'  -- ?     end; $func$   language sql immutable; 

call:

select *, t.add_cost items1 t; 

note table-qualification in t.add_cost. demonstrate syntax variant since have been asking it. advise use less confusing standard syntax:

select *, add_cost(t) add_cost items1 t;  -- column alias optional 

however, sql strictly typed language. if define particular row type input parameter, bound particular row type. passing various whole table types more sophisticated, still possible polymorphic input type.

create or replace function add_cost(anyelement)  -- function name = default col name   returns numeric $func$ select      case          when $1.labor < 100 , $1.overhead < .20 numeric '1' --      when ..... --      when ..... --      when .....         else numeric '0'  -- ?     end; $func$   language sql immutable; 

same call table has columns labor , overhead matching data type.

dbfiddle here

also see related simple case passing simple values here:

• how put part of select statement postgres function

for more complex requirements - returning various row types - see:

• refactor pl/pgsql function return output of various select queries