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

i have part of select statement pretty lengthy set of conditional statements. want put function can call more efficiently on table need use on.

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; 

is possible add part of select function can injected calling function?

i sorting through documentation , google, , seems might possible if creating function in plpgsql language opposed sql. however, reading isn't clear.

you cannot wrap any part of select statement function. expression case can wrapped:

create or replace function  pg_temp.calc_add_cost(_labor integer, _overhead numeric)   returns numeric $func$ select      case          when _labor < 100 , _overhead < .20 numeric '1'  -- example value --      when ..... --      when ..... --      when .....         else numeric '0'  -- example value     end; $func$   language sql immutable; 

while could use pl/pgsql this, demo simple sql function. see:

• difference between language sql , language plpgsql in postgresql functions

adapt input , output data types need. guessing integer , numeric lack of information.

call:

select calc_add_cost(1, 0.1); 

in statement:

select      itemnumber,      itemname,      base,      calc_add_cost(labor, overhead) add_cost,  -- pass column values input     gpm items1; 

you should understand basics postgres functions make proper use. might start manual page on create function.

there many related questions & answers here on so.

also see related, more sophisticated case passing whole rows here:

• pass table name used in function automatically in postgresql 9.6.3