Postgres C Function - Passing & Returning Numerics

Question

I am just beginning to test with Postgres External C Functions. When I pass in a Numeric and Return it the function works fine. (Example)

Sample Function

PG_FUNCTION_INFO_V1(numericTesting);
Datum
numericTesting(PG_FUNCTION_ARGS)
{
    Numeric       p = PG_GETARG_NUMERIC(0);
    PG_RETURN_NUMERIC(p);
}

However, when I try to do any math functions on the variable passed in, it will not compile. I get

error: invalid operands to binary *

Sample Function

PG_FUNCTION_INFO_V1(numericTesting);
Datum
numericTesting(PG_FUNCTION_ARGS)
{
    Numeric       p = PG_GETARG_NUMERIC(0);
    PG_RETURN_NUMERIC(p * .5);
}

What is causing this? I'm guessing the the Numeric datatype needs some function to allow math. I tried using: PG_RETURN_NUMERIC(DatumGetNumeric(p * .5)) but that had the same result.

This version works but requires two Numerics to be used. Need to figure out how to instantiate a Numeric from an Integer or Float8 I suppose. PG_FUNCTION_INFO_V1(numericTesting); Datum numericTesting(PG_FUNCTION_ARGS) { Numeric p = PG_GETARG_NUMERIC(0); Numeric answer = DatumGetNumeric(DirectFunctionCall2(numeric_mul,NumericGetDatum(p_latitude), NumericGetDatum(p_latitude))); PG_RETURN_NUMERIC(answer); } — user739866, Sep 25 '12 at 18:52

Craig Ringer · Answer 1 · 2012-09-27T03:46:23.190

Numeric isn't a primitive type so you can't do arithmetic operations on it directly. C doesn't have operator overloading, so there's no way to add a multiply operator for Numeric. You'll have to use appropriate function calls to multiply numerics.

As with most things when writing Pg extension functions it can be helpful to read the source and see how it's done elsewhere.

In this case look at src/backend/utils/adt/numeric.c. Examine Datum numeric_mul(PG_FUNCTION_ARGS) where you'll see it use mul_var(...) to do the work.

Unfortunately mul_var is static so it can't be used outside numeric.c. Irritating and surprising. There must be a reasonable way to handle NUMERIC from C extension functions without using the spi/fmgr to do the work via SQL operator calls, as you've shown in your comment where you use DirectFunctionCall2 to invoke the numeric_mul operator.

It looks like the public stuff for Numeric that's callable directly from C is in src/include/utils/numeric.h so let's look there. Whoops, not much, just some macros for converting between Numeric and Datum and some helper GETARG and RETURN macros. Looks like usage via the SQL calls might be the only way.

If you do find yourself stuck using DirectFunctionCall2 via the SQL interfaces for Numeric, you can create a Numeric argument for the other side from a C integer using int4_numeric.

If you can't find a solution, post on the pgsql-general mailing list, you'll get more people experienced with C extensions and the source code there. Link back to this post if you do so.

score 1 · Answer 2 · answered Feb 23 '18 at 21:36

A way to sidestep this problem altogether is to use data type coercion. Declare your SQL function with the type you want to coerce a value to, e.g.

CREATE FUNCTION foo(float8) RETURNS float8 AS 'SELECT $i' LANGUAGE SQL;

Any value provided to that function will be coerced to that value:

SELECT foo(12);

Even explicitly specifying the type will work:

SELECT foo(12::numeric);

Your C code will receive a double. (Credit goes to Tom Lane, see this mailing list post.)

score 0 · Answer 3 · answered Sep 25 '12 at 18:14

0

Both operands of * must have arithmetic type (integer or floating-point types). It's a pretty good bet that Numeric is a typedef for something that isn't a simple integer or floating-point type.

Unfortunately, I don't know enough about the Postgres API to be much more help. Hopefully there's a macro or a function that can either convert a Numeric to an arithmetic type, or apply an arithmetic operation to a Numeric type.

answered Sep 25 '12 at 18:14

John Bode

119,563
19
122
198

You are definitely right. I am trying to find out what exactly. I put an edit above that works but only when passing in 2 objects of type Numeric. – user739866 Sep 25 '12 at 18:51
grep the source code for `numeric_to_double_no_overflow` and/or `PGTYPESnumeric_to_double`. The code will show how to perform the translation from the `struct` to a `double`. Not a full answer as data type coercion was the solution for me (see my answer), but this is at least a pointer. – Demitri Feb 23 '18 at 21:40

score 0 · Answer 4 · answered Jan 11 '23 at 17:35

0

#include "utils/numeric.h"

// ....

Numeric p = PG_GETARG_NUMERIC(0);
Numeric b = int64_div_fast_to_numeric(5, 1); // 0.5

bool failed = false;
Numeric r = numeric_mul_opt_error(p, b, &failed);
if (failed) {
   // handle failure here
}

PG_RETURN_NUMERIC(r);

answered Jan 11 '23 at 17:35

Marco Kinski

302
3
7

1

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Jan 13 '23 at 16:09

jian · Answer 5 · 2023-05-30T09:57:47.490

based on Marco Kinski answer.

/home/jian/postgres/2023_05_25_beta5421/bin/pg_config  --includedir-server

output is

/home/jian/postgres/2023_05_25_beta5421/include/server

That's how gcc link postgres server include header files.

/*
 /home/jian/Desktop/regress_pgsql/test_numeric.c
 https://stackoverflow.com/questions/12588554/postgres-c-function-passing-returning-numerics/75086964#75086964
--compile and link.
gcc -I/home/jian/postgres/2023_05_25_beta5421/include/server -fPIC -c /home/jian/Desktop/regress_pgsql/test_numeric.c
gcc -shared  -o /home/jian/Desktop/regress_pgsql/test_numeric.so /home/jian/Desktop/regress_pgsql/test_numeric.o
*/    

    #include "postgres.h"
    
    #include "utils/numeric.h"
    #include "funcapi.h"
    
    PG_MODULE_MAGIC;
    PG_FUNCTION_INFO_V1(test_numeric);
    
    Datum 
    test_numeric(PG_FUNCTION_ARGS)
    {
    
        Numeric p = PG_GETARG_NUMERIC(0);
        Numeric b = int64_div_fast_to_numeric(5, 1); // 0.5
        bool failed = false;
        Numeric r = numeric_mul_opt_error(p, b, &failed);
        if (failed) 
            ereport(ERROR,
                    (errmsg(" failed,.")));
    
        PG_RETURN_NUMERIC(r);
    }

int64_div_fast_to_numeric: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/numeric.c;h=6bf6db6e27bbb0ca8fe904f41b37540c34aa6bf6;hb=b2d47928908d7a99b8e39198d0e8e9e0cb2b024b#l4242

numeric_mul_opt_error: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/numeric.c;h=6bf6db6e27bbb0ca8fe904f41b37540c34aa6bf6;hb=b2d47928908d7a99b8e39198d0e8e9e0cb2b024b#l3004

load it:

    CREATE OR REPLACE FUNCTION test_numeric(numeric)
    RETURNS numeric SET search_path from current
    AS '/home/jian/Desktop/regress_pgsql/test_numeric', 'test_numeric'
    LANGUAGE c IMMUTABLE STRICT;

except numeric.h exposed numeric related functions in utils/fmgr/protos.h

341:extern Datum hash_numeric(PG_FUNCTION_ARGS);
464:extern Datum hash_numeric_extended(PG_FUNCTION_ARGS);
785:extern Datum numeric_fac(PG_FUNCTION_ARGS);
966:extern Datum numeric_in(PG_FUNCTION_ARGS);
967:extern Datum numeric_out(PG_FUNCTION_ARGS);
968:extern Datum numeric(PG_FUNCTION_ARGS);
969:extern Datum numeric_abs(PG_FUNCTION_ARGS);
970:extern Datum numeric_sign(PG_FUNCTION_ARGS);
971:extern Datum numeric_round(PG_FUNCTION_ARGS);
972:extern Datum numeric_trunc(PG_FUNCTION_ARGS);
973:extern Datum numeric_ceil(PG_FUNCTION_ARGS);
974:extern Datum numeric_floor(PG_FUNCTION_ARGS);
980:extern Datum numeric_eq(PG_FUNCTION_ARGS);
981:extern Datum numeric_ne(PG_FUNCTION_ARGS);
982:extern Datum numeric_gt(PG_FUNCTION_ARGS);
983:extern Datum numeric_ge(PG_FUNCTION_ARGS);
984:extern Datum numeric_lt(PG_FUNCTION_ARGS);
985:extern Datum numeric_le(PG_FUNCTION_ARGS);
986:extern Datum numeric_add(PG_FUNCTION_ARGS);
987:extern Datum numeric_sub(PG_FUNCTION_ARGS);
988:extern Datum numeric_mul(PG_FUNCTION_ARGS);
989:extern Datum numeric_div(PG_FUNCTION_ARGS);
990:extern Datum numeric_mod(PG_FUNCTION_ARGS);
991:extern Datum numeric_sqrt(PG_FUNCTION_ARGS);
992:extern Datum numeric_exp(PG_FUNCTION_ARGS);
993:extern Datum numeric_ln(PG_FUNCTION_ARGS);
994:extern Datum numeric_log(PG_FUNCTION_ARGS);
995:extern Datum numeric_power(PG_FUNCTION_ARGS);
996:extern Datum int4_numeric(PG_FUNCTION_ARGS);
997:extern Datum float4_numeric(PG_FUNCTION_ARGS);
998:extern Datum float8_numeric(PG_FUNCTION_ARGS);
999:extern Datum numeric_int4(PG_FUNCTION_ARGS);
1000:extern Datum numeric_float4(PG_FUNCTION_ARGS);
1001:extern Datum numeric_float8(PG_FUNCTION_ARGS);
1006:extern Datum numeric_inc(PG_FUNCTION_ARGS);
1008:extern Datum numeric_smaller(PG_FUNCTION_ARGS);
1009:extern Datum numeric_larger(PG_FUNCTION_ARGS);
1011:extern Datum numeric_cmp(PG_FUNCTION_ARGS);
1013:extern Datum numeric_uminus(PG_FUNCTION_ARGS);
1014:extern Datum numeric_to_char(PG_FUNCTION_ARGS);
1019:extern Datum numeric_to_number(PG_FUNCTION_ARGS);
1021:extern Datum numeric_int8(PG_FUNCTION_ARGS);
1023:extern Datum int8_numeric(PG_FUNCTION_ARGS);
1024:extern Datum int2_numeric(PG_FUNCTION_ARGS);
1025:extern Datum numeric_int2(PG_FUNCTION_ARGS);
1048:extern Datum numeric_accum(PG_FUNCTION_ARGS);
1052:extern Datum numeric_avg(PG_FUNCTION_ARGS);
1053:extern Datum numeric_var_samp(PG_FUNCTION_ARGS);
1054:extern Datum numeric_stddev_samp(PG_FUNCTION_ARGS);
1098:extern Datum numeric_uplus(PG_FUNCTION_ARGS);
1142:extern Datum numeric_div_trunc(PG_FUNCTION_ARGS);
1209:extern Datum width_bucket_numeric(PG_FUNCTION_ARGS);
1415:extern Datum numeric_recv(PG_FUNCTION_ARGS);
1416:extern Datum numeric_send(PG_FUNCTION_ARGS);
1469:extern Datum numeric_var_pop(PG_FUNCTION_ARGS);
1512:extern Datum numeric_stddev_pop(PG_FUNCTION_ARGS);
1549:extern Datum numeric_avg_serialize(PG_FUNCTION_ARGS);
1550:extern Datum numeric_avg_deserialize(PG_FUNCTION_ARGS);
1618:extern Datum numeric_avg_accum(PG_FUNCTION_ARGS);
1658:extern Datum numerictypmodin(PG_FUNCTION_ARGS);
1659:extern Datum numerictypmodout(PG_FUNCTION_ARGS);
1854:extern Datum numeric_support(PG_FUNCTION_ARGS);
1862:extern Datum pg_size_pretty_numeric(PG_FUNCTION_ARGS);
1873:extern Datum numeric_sum(PG_FUNCTION_ARGS);
1917:extern Datum generate_series_step_numeric(PG_FUNCTION_ARGS);
1918:extern Datum generate_series_numeric(PG_FUNCTION_ARGS);
1936:extern Datum numeric_scale(PG_FUNCTION_ARGS);
1938:extern Datum numeric_sortsupport(PG_FUNCTION_ARGS);
1980:extern Datum numeric_serialize(PG_FUNCTION_ARGS);
1981:extern Datum numeric_deserialize(PG_FUNCTION_ARGS);
1982:extern Datum numeric_avg_combine(PG_FUNCTION_ARGS);
1983:extern Datum numeric_poly_combine(PG_FUNCTION_ARGS);
1984:extern Datum numeric_poly_serialize(PG_FUNCTION_ARGS);
1985:extern Datum numeric_poly_deserialize(PG_FUNCTION_ARGS);
1986:extern Datum numeric_combine(PG_FUNCTION_ARGS);
2009:extern Datum numeric_poly_sum(PG_FUNCTION_ARGS);
2010:extern Datum numeric_poly_avg(PG_FUNCTION_ARGS);
2011:extern Datum numeric_poly_var_pop(PG_FUNCTION_ARGS);
2012:extern Datum numeric_poly_var_samp(PG_FUNCTION_ARGS);
2013:extern Datum numeric_poly_stddev_pop(PG_FUNCTION_ARGS);
2014:extern Datum numeric_poly_stddev_samp(PG_FUNCTION_ARGS);
2060:extern Datum jsonb_numeric(PG_FUNCTION_ARGS);
2132:extern Datum numeric_accum_inv(PG_FUNCTION_ARGS);
2327:extern Datum cash_numeric(PG_FUNCTION_ARGS);
2328:extern Datum numeric_cash(PG_FUNCTION_ARGS);
2532:extern Datum in_range_numeric_numeric(PG_FUNCTION_ARGS);
2655:extern Datum brin_minmax_multi_distance_numeric(PG_FUNCTION_ARGS);
2701:extern Datum numeric_min_scale(PG_FUNCTION_ARGS);
2702:extern Datum numeric_trim_scale(PG_FUNCTION_ARGS);
2707:extern Datum numeric_gcd(PG_FUNCTION_ARGS);
2708:extern Datum numeric_lcm(PG_FUNCTION_ARGS);
2747:extern Datum numeric_pg_lsn(PG_FUNCTION_ARGS);

Postgres C Function - Passing & Returning Numerics

5 Answers5

Linked