PostgreSQL C function to get values

Question

I am trying to write a PostgreSQL function in C.

My goal is finding minimum value of a list. So, my function will be executed like these:

SELECT min_to_max(val) FROM (VALUES(1),(2),(3)) x(val);
SELECT min_to_max(val) FROM my_table;

Here is my C code and I lost here. For example, there is a function called "PG_GETARG_INT32" to get integer values, but I don' t know how to get values from a table in order to process. Any idea?

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(get_sum);

Datum
get_sum(PG_FUNCTION_ARGS)
{   
    ArrayType  *v1,
    bool isnull;

    isnull = PG_ARGISNULL(0);
    if (isnull)
      ereport( ERROR,
               ( errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
               errmsg("The input cannot be empty")));
    
    List a = PG_GETARR_SOMEFUNCTION_2_GET_LIST(0);

    # for loop iteration to find min_val

    # return min_val
}

Edited(2022.05.13 - below is edited part):

Thanks to @Laurenz Albe. I made some progress.

Yet, now I want to go further(No needs to be in C language. As Laurenz Albe stated, I am just taking small steps).

My functions and aggregates like below to find min and max:

CREATE or replace FUNCTION find_min_func(
    state integer,
    next  integer
) RETURNS integer
LANGUAGE plpgsql
STRICT
AS $$
declare
min_val integer;                      
begin                                 
if $1 <= $2 then min_val := $1;       
elsif $2 <$1 then min_val := $2;      
end if;                               
return min_val;                       
END;
$$;

CREATE or replace AGGREGATE find_min(integer)
(
    SFUNC    = find_min_func,
    STYPE    = integer
);

CREATE or replace FUNCTION find_max_func(
    state integer,
    next  integer
) RETURNS integer
LANGUAGE plpgsql
STRICT
AS $$
declare
max_val integer;                      
begin                                 
if $1 >= $2 then max_val := $1;       
elsif $2 > $1 then max_val := $2;      
end if;                               
return max_val;                       
END;
$$;

CREATE or replace AGGREGATE find_max(integer)
(
    SFUNC    = find_max_func, -- State function
    STYPE    = integer       -- State type
);

They are working great but now I want to do something like

SELECT min_to_max(val) FROM (VALUES(1),(2),(3)) x(val);

Expected output: 1 -> 3

So, I just wrote a state function and aggregate pair like below(I know it is wrong):

CREATE or replace FUNCTION find_min_and_max_func(
    state integer,
    next  integer
) RETURNS varchar
LANGUAGE plpgsql
STRICT
AS $$
declare
min_val integer;  
max_val integer;   
output varchar;                   
begin    
if $1 <= $2 then min_val := $1;   max_val := $2;      
elsif $2 <$1 then min_val := $2;   max_val := $1;
end if;
output = cast(min_val as varchar) || '->' ||     cast(max_val as varchar)       ;             
return output;                       
END;
$$;

CREATE or replace AGGREGATE find_min_and_max(integer)
(
    SFUNC    = find_min_and_max_func, -- State function
    STYPE    = varchar       -- State type
);

It is wrong because state function is taking arguments as integer but returns(?) varchar, so it varying.

How can I arrange my state function here?

Thanks!

Laurenz Albe · Accepted Answer · 2022-05-11 13:02:06Z

1

To create an aggregate function, you have to use CREATE AGGREGATE. You create that in SQL. What you may need to implement in C is the state transition function (SFUNC), perhaps also others, depending on the kind of aggregate you want to create.

The aggregate function does not have to read from the table; the PostgreSQL executor will feed it the data it needs.

If you start writing C functions, you should perhaps start with something simpler than aggregate functions.

answered May 11, 2022 at 13:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Umut TEKİN Over a year ago

Thanks for the comment. I made some progress and need to go further. Any idea about my updated problem?

Umut TEKİN Over a year ago

I guess solution is in the final function in the aggregation functions.

Laurenz Albe Over a year ago

You only need a "final function" if the intermediate state is different from the result.

Umut TEKİN Over a year ago

Thank you for your help. Reading what you wrote is really helpful. I found a way now. I will post my last notes. I hope we meet in a conference in Europe.

Umut TEKİN · Accepted Answer · 2022-05-13 16:32:37Z

With the help of @Laurenz Albe(thanks to him again), I found the solution. Also, I checked out:

Here is my solution:

CREATE or replace FUNCTION find_min_and_max_func(
    state point,
    next  integer
) RETURNS point
LANGUAGE plpgsql
STRICT
AS $$
declare
min_val integer;  
max_val integer;                   
begin    
if state[0] <= next then min_val := state[0];  
elsif next < state[0] then min_val := next;  
end if;
if state[1] >= next then max_val := state[1];  
elsif next > state[1] then max_val := next;  
end if;               
return point(min_val, max_val) ;                     
END;
$$;

CREATE or replace FUNCTION find_min_and_max_final_func(
    state point
) RETURNS varchar
LANGUAGE plpgsql
STRICT
AS $$                 
begin              
return cast(state[0] as varchar) || '->' || cast(state[1] as varchar) ;                     
END;
$$;


CREATE or replace AGGREGATE find_min_and_max(integer)
(
    SFUNC    = find_min_and_max_func, -- State function
    STYPE    = point,       -- State type
    FINALFUNC = find_min_and_max_final_func,
    initcond = '(1231231232131,0)'
);

SELECT find_min_and_max(value) FROM UNNEST(ARRAY [1, 2, 3]) as value;

 find_min_and_max
------------------
 1->6
(1 row)

Thanks!

Collectives™ on Stack Overflow

PostgreSQL C function to get values

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related