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
– Ankit Bajpai, Commented Sep 11, 2017 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
– Erwin Brandstetter, Commented Sep 11, 2017 at 23:16
BTW, you just closed your related question (stackoverflow.com/questions/46164931/…) - 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
– Erwin Brandstetter, Commented Sep 11, 2017 at 23:57
Thanks! I thought your answer below helped answer that question, but I am definitely open for any additional information. — cheslijones
– cheslijones, Commented Sep 12, 2017 at 0:02
@ErwinBrandstetter, Thanks for correcting m. I never know that PostGres never supports procedures. — Ankit Bajpai
– Ankit Bajpai, Commented Sep 12, 2017 at 8:42

Erwin Brandstetter · Accepted Answer · 2017-09-12 00:14:52Z

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, 2017 at 0:14

answered Sep 11, 2017 at 23:08

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andreas Over a year ago

What if he needs a bunch of columns from the table. How would you pass the whole row to the function?

Erwin Brandstetter Over a year ago

@Andreas: You can pass a whole row. Syntax would be SELECT calc_add_cost(t) FROM mytable t; See: stackoverflow.com/questions/31148157/… 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.

cheslijones Over a year ago

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?

Erwin Brandstetter Over a year ago

@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: stackoverflow.com/q/46164931/939860

Erwin Brandstetter Over a year ago

@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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related