-1

is there a way to create a customized generic function in PostgreSQL? I would like to create a custom FLOOR function that accepts two parameters and as a result returns a new column in the query result. The first parameter should be the the column of a table and the second the number of significant digits. For example, there is a table (table_foo) with a float column (column_bar). A typical query could be like:

SELECT column_bar, FLOOR(column_bar) FROM table_foo;

I would like a generic function to make similar queries:

SELECT column_bar, CUSTOM_FLOOR(column_bar, 3) FROM table_foo;

I mention the FLOOR function as an example, but it could be a generic function that could be used by any database and any table, the same way FLOOR, POWER etc. are used.

3
  • This an almost exact duplicate of the previous question you asked, which was subsequently closed. Please don't re-ask the exact same question like this. Instead, edit your original question as directed in the banner shown at the top of the question. In this particular case, the question is not relevant to the wider audience of our site since creating a custom function is mentioned directly in the PostgreSQL documentation at postgresql.org/docs/current/sql-createfunction.html
    – Hannah Vernon
    Dec 9 '20 at 19:25
  • If you've already attempted to create a specific function, and are having trouble getting it to work, please add a minimal reproducible example showing the function code and the problem.
    – Hannah Vernon
    Dec 9 '20 at 19:28
  • I made many attempts but i was getting the cross join of the function result. The answers helped me. Sorry for the inconvenience.
    – jorgeb
    Dec 10 '20 at 10:43
0

Do you mean something like

CREATE FUNCTION custom_floor(anyelement, integer) RETURNS anyelement
   LANGUAGE sql AS
'SELECT floor($1 * (10 ^ $2)) / (10 ^ $2)';
0

PostgrSQL supports the use of custom defined functions. This would allow you to either create your own custom function, MyCustomFunction or even replace an existing function. Using the above link you could create a function such as

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.