Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 568
Uncle Milton Avatar asked Jun 15 '16 02:06

Uncle Milton


People also ask

Can we return table from function in PostgreSQL?

To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.

Can PostgreSQL stored procedure return resultset?

You can call a PostgreSQL stored procedure and process a result set in a . NET application, for example, in C# application using Npgsql . NET data provider. Note that you do not need to know the name of the cursor to process the result set.

How query is execute in PostgreSQL?

To do this in PL/pgSQL, use the PERFORM statement: PERFORM query ; This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM .

What is return query in Postgres?

RETURN QUERY appends the results of executing a query to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.


1 Answers

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;
like image 90
Patrick Avatar answered Oct 19 '22 23:10

Patrick