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

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;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related