CODEWITHSUNDEEP
postgresqlfunctionplpgsqlprocedural-programming
Uncle Milton
Uncle Milton

Reputation: 183

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

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?

Upvotes: 8

Views: 7987

Answers (1)

Patrick
Patrick

Reputation: 32161

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;

Upvotes: 4

Related Questions