Pass table name used in FROM to function automatically?

Question

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 other columns which are factored into the "additional cost". I would like to put the calculation into a function so I can call it for any of these tables.

So instead of:

SELECT 
    itemnumber, 
    itemname, 
    base, 
    CASE 
        WHEN labor < 100 AND overhead < .20 THEN
        WHEN .....
        WHEN .....
        WHEN .....
        .....
    END AS add_cost,
    gpm
FROM items1;

I can just do:

SELECT 
    itemnumber, 
    itemname, 
    base, 
    calc_add_cost(),
    gpm
FROM items1;

If I want to be able to use it on any of the item tables, I guess I would need to set a table_name parameter that the function takes since adding the table name into the function would be undesirable to say the least.

calc_add_cost(items1)

However, is there a simpler way such that when I call calc_add_cost() it will just use the table name from the FROM clause?

SELECT ....., calc_add_cost(item1) FROM item1

Just seems redundant.

I did come across a few topics with titles that sounded like they addressed what I was hoping to accomplish, but upon reviewing them it looked like they were a different issue.

You may consider passing the row, or even just the two param values like ´calc_add_cost(labor, overhead)´. If you get the table, you would have to re-query it (and then you need to know which row to use..) — JGH, Sep 11 '17 at 23:19

Erwin Brandstetter · Accepted Answer · 2017-09-12T00:36:11.640

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:

CREATE OR REPLACE FUNCTION add_cost(items1)  -- function name = default col name
  RETURNS numeric AS
$func$
SELECT 
    CASE 
        WHEN $1.labor < 100 AND $1.overhead < .20 THEN numeric '1'
--      WHEN .....
--      WHEN .....
--      WHEN .....
        ELSE numeric '0'  -- ?
    END;
$func$
  LANGUAGE sql 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 alias is 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.

CREATE OR REPLACE FUNCTION add_cost(ANYELEMENT)  -- function name = default col name
  RETURNS numeric AS
$func$
SELECT 
    CASE 
        WHEN $1.labor < 100 AND $1.overhead < .20 THEN numeric '1'
--      WHEN .....
--      WHEN .....
--      WHEN .....
        ELSE numeric '0'  -- ?
    END;
$func$
  LANGUAGE sql 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

Pass table name used in FROM to function automatically?

1 Answers1

Linked