Pass table to postgreSQL function, execute select statement, return table

Question

I'm transitioning to postgreSQL from MSSQL and still working out the syntax for procedural programming. The idea is to create a function that will take a table as an input/parameter, execute a series of SELECT statements against this input table (no temp tables; the SELECT statements will be executed with CTEs), and RETURN a final table.

The input table would be very straightforward:

Col_1      Col_2       Col_3
----------------------------
2           5           12

I want to use each of the fields in the input table to calculate a number (in this example, summing Col_1, Col_2, and Col_3), append it to the input table, and create an output table that appears as such:

Col 1      Col_2       Col_3      Col_4
---------------------------------------
2           5           12        19

My attempt at the code:

CREATE OR REPLACE FUNCTION summing_function(input_table)
RETURNS output_table(Col_1 INT, Col_2 INT, Col_3 INT, Col_4 INT) AS
$$
SELECT 
    i.*
    , i."Col_1" + i."Col_2" + i."Col_3" as sum
INTO output_table
FROM input_table i
$$
LANGUAGE SQL;

Obviously everything between the dollar quotes is incomplete/wrong. What's the correct postgreSQL syntax for piece between the dollar quotes?

Isn't a view what you want to use instead ? (this question is not a way to say you should use a view, but a real question from me to understand why a view can't be the right tool to use here) — iXô
– iXô, Commented Jun 15, 2016 at 7:55
A view could very well be the better solution. I was just trying to get some clarification on general postgreSQL syntax and constraints. — Uncle Milton
– Uncle Milton, Commented Jun 15, 2016 at 20:45

Patrick · Accepted Answer · 2016-06-15 07:52:17Z

5

You can not pass a "table" as a function argument. You can pass the name of a table to a PL/pgSQL function and then run a dynamic query:

CREATE OR REPLACE FUNCTION summing_function(input_table text)
    RETURNS TABLE (col_1 int, col_2 int, col_3 int, col_4 int) AS $$
BEGIN
    RETURN QUERY EXECUTE
        format('SELECT *, col_1 + col_2 + col_3 FROM %I', input_table);
END;
$$ LANGUAGE plpgsql;

answered Jun 15, 2016 at 7:52

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Pass table to postgreSQL function, execute select statement, return table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related