Composition of high complex queries in postgresql

user_12320458 0
Viewed 45

My question is: How can I replace some complex calculation using custom defined functions in such way that the statements from the query who called the function affect the function output in postgres? I will give an example to clarify it a little bit more. Assume we have the following table:

CREATE TABLE test (my_id INT, cat_col VARCHAR(1), col1 INT, col2 INT);
INSERT INTO test
VALUES
(0, 'a', 1, 3),
(1, 'a', 4, 5),
(2, 'b', 2, 8),
(3, 'b', 3, 6);

Consider the following query:

SELECT cat_col, SUM(col1 * col2)
FROM test
WHERE my_id >= 1
GROUP BY cat_col;

Let's pretend the statement SUM(col1 * col2) is very complex and we cannot easily put it in the query just like we did, how can I write a function to replace this statement? Something like:

SELECT cat_col, f(params)
FROM test
WHERE my_id >= 1
GROUP BY cat_col;

I think there must be a way to pass the filtered and grouped column to the function, but I do not have two much experience with user defined functions and after reading the postgres documentation i could not come up with a solution yet. Can someone help me out?

calculated-columns postgresql sql
1 Answers

You should know that when you declare a function, the query will be much slower. Aggregate functions are faster than calling a user defined function. Nevertheless, function could look like (PG/PL SQL block):

CREATE OR REPLACE FUNCTION sum_up(i integer, y integer) RETURNS integer AS $$
BEGIN
    RETURN i + y;
END;
$$ LANGUAGE plpgsql;

You could also use SQL function:

CREATE FUNCTION sum_up(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

You can read more about declaring different types of function in documentation: https://www.postgresql.org/docs/current/sql-createfunction.html

user_38379610
Related