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

Question

I have part of a SELECT statement that is a pretty lengthy set of conditional statements. I want to put it into a function so I can call it much more efficiently on any table I need to use it on.

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;

Is it possible to add part of a SELECT to a function so that can be injected just by calling the function?

I am sorting through documentation and Google, and it seems like it might be possible if creating the function in the plpgsql language as opposed to sql. However, what I am reading isn't very clear.

You need a stored procedure for the case statement. Just google it and your problem will be solved. — Ankit Bajpai, Sep 11 '17 at 22:23
@AnkitBajpai: There are no true "stored procedures" in Postgres. Just functions, doing almost but not quite the same. (And all we need here is a function.) — Erwin Brandstetter, Sep 11 '17 at 23:16
BTW, you just closed your related question (https://stackoverflow.com/questions/46164931/pass-the-table-name-used-in-from-to-function-automatically-in-postgresql-9-6-3) - there *is* a way to use variable table types as input - and make this look like a generated column. If you still need this, re-open that other question. — Erwin Brandstetter, Sep 11 '17 at 23:57
Thanks! I thought your answer below helped answer that question, but I am definitely open for any additional information. — cjones, Sep 12 '17 at 00:02
@ErwinBrandstetter, Thanks for correcting m. I never know that PostGres never supports procedures. — Ankit Bajpai, Sep 12 '17 at 08:42
@AnkitBajpai: It's a bit of nitpicking, because functions are called "procedures", too. But "stored procedures" as defined in the SQL standard allow more than what fits inside a single transaction. — Erwin Brandstetter, Sep 12 '17 at 15:20

Erwin Brandstetter · Accepted Answer · 2017-09-12T00:14:52.917

2

You cannot just wrap any part of a SELECT statement into a function. But an expression like your CASE can easily be wrapped:

CREATE OR REPLACE FUNCTION  pg_temp.calc_add_cost(_labor integer, _overhead numeric)
  RETURNS numeric AS
$func$
SELECT 
    CASE 
        WHEN _labor < 100 AND _overhead < .20 THEN numeric '1'  -- example value
--      WHEN .....
--      WHEN .....
--      WHEN .....
        ELSE numeric '0'  -- example value
    END;
$func$
  LANGUAGE sql IMMUTABLE;

While you could also use PL/pgSQL for this, the demo is a simple SQL function. See:

Difference between language sql and language plpgsql in PostgreSQL functions

Adapt input and output data types to your need. Just guessing integer and numeric for lack of information.

Call:

SELECT calc_add_cost(1, 0.1);

In your statement:

SELECT 
    itemnumber, 
    itemname, 
    base, 
    calc_add_cost(labor, overhead) AS add_cost,  -- pass column values as input
    gpm
FROM items1;

You should understand some basics about Postgres functions to make proper use. Might start with the manual page on CREATE FUNCTION.

There are also many related questions & answers here on SO.

Also see the related, more sophisticated case passing whole rows here:

Pass the table name used in FROM to function automatically in PostgreSQL 9.6.3

edited Sep 12 '17 at 00:14

answered Sep 11 '17 at 23:08

Erwin Brandstetter

605,456
145
1,078
1,228

What if he needs a bunch of columns from the table. How would you pass the whole row to the function? – Andreas Sep 11 '17 at 23:18
2

@Andreas: You can pass a whole row. Syntax would be `SELECT calc_add_cost(t) FROM mytable t;` See: https://stackoverflow.com/questions/31148157/how-do-i-pass-in-a-table-parameter-to-this-function/31167928#31167928 But since the OP wants to use the function `on any table I need to use it on`, it would be unwise to bind the function to a particular row type as input. There are composite types, too. All possible, it depends on the exact use case. – Erwin Brandstetter Sep 11 '17 at 23:22
Hey, thanks! That helped me a lot in getting started and helped answer another question I had posted today. Regarding passing a whole row, this table has about 50 columns of which maybe 8 will be passed into this function. At what point does it make sense to pass the whole row? And also, it sounds like if I pass the whole row, I don't have to specify the columns to pass into the function as arguments, but I can specify those columns inside the function? In other words, passing the row gives me access to the columns without specifying them when calling the function? – cjones Sep 12 '17 at 00:01
@Andreas: On second though, we can make this work for variable table types with polymorphic input. Pretty elegant, too. I added a demo to sockpuppet's other question here: https://stackoverflow.com/q/46164931/939860 – Erwin Brandstetter Sep 12 '17 at 00:12
@sockpuppet: Yes, passing the whole row gives you access to all columns of the row. But you need to know the column names in advance to access individual columns in the function body. Well, there may be ways around this, too, like casting to JSON ... but there are pitfalls. – Erwin Brandstetter Sep 12 '17 at 00:19
Got it, I will experiment with this, this morning. Any chance you could provide input on: https://stackoverflow.com/questions/46163213/neither-percentile-cont-nor-percentile-disc-are-calculating-the-desired-75th-per – cjones Sep 12 '17 at 15:36

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

1 Answers1

Linked