Pass Table Name Used In From To Function Automatically?

Working with PostgreSQL 9.6.3. I am new to functions in databases. Let's say there are multiple tables of item numbers. Each one has the item number, the item cost and several othe

Solution 1:

You can even emulate a "computed field" or "generated column" like you had in mind. Basics here:

• Store common query as column?

Simple demo for one table:

CREATEOR REPLACE FUNCTION add_cost(items1)  -- function name = default col nameRETURNSnumericAS
$func$
SELECTCASEWHEN $1.labor <100AND $1.overhead <.20THENnumeric'1'--      WHEN .....--      WHEN .....--      WHEN .....ELSEnumeric'0'-- ?END;
$func$
  LANGUAGEsql IMMUTABLE;

Call:

SELECT*, t.add_cost FROM items1 t;

Note the table-qualification in t.add_cost. I only demonstrate this syntax variant since you have been asking for it. My advise is to use the less confusing standard syntax:

SELECT *, add_cost(t) AS add_cost FROM items1 t;  -- column aliasis also optional

However, SQL is a strictly typed language. If you define a particular row type as input parameter, it is bound to this particular row type. Passing various whole table types is more sophisticated, but still possible with polymorphic input type.

CREATEOR REPLACE FUNCTION add_cost(ANYELEMENT)  -- function name = default col nameRETURNSnumericAS
$func$
SELECTCASEWHEN $1.labor <100AND $1.overhead <.20THENnumeric'1'--      WHEN .....--      WHEN .....--      WHEN .....ELSEnumeric'0'-- ?END;
$func$
  LANGUAGEsql IMMUTABLE;

Same call for any table that has the columns labor and overhead with matching data type.

dbfiddle here

Also see the related simple case passing simple values here:

• How to put part of a SELECT statement into a Postgres function

For even more complex requirements - like also returning various row types - see:

• Refactor a PL/pgSQL function to return the output of various SELECT queries