Thread: 38.10.6. Composite-Type Arguments C-language function code demo works for int, but not for numeric.
38.10.6. Composite-Type Arguments C-language function code demo works for int, but not for numeric.
From
jian he
Date:
Hi.
source: https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-C-BASETYPE (38.10.6. Composite-Type Arguments)
column "salary" int data type works fine. But it does not work if the "salary" column data type is numeric.
/*
rm funcs.so && rm funcs.o
gcc -I/home/jian/postgres/pg16/include/postgresql/server -fPIC -c funcs.c
gcc -shared -o funcs.so funcs.o
*/
#include "postgres.h"
#include "executor/executor.h"
#include "utils/numeric.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(c_overaid);
Datum
c_overaid(PG_FUNCTION_ARGS)
{
HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
Numeric limit = PG_GETARG_NUMERIC_COPY(1);
bool isnull;
Datum salary;
salary = GetAttributeByName(t,"salary",&isnull);
if(isnull)
PG_RETURN_BOOL(false);
/* alternatively, we might prefer to do PG_RETURN_NULL() for null salary */
PG_RETURN_BOOL(DatumGetNumericCopy(salary) > limit);
}
---------------------------------------------------------------------------
rm funcs.so && rm funcs.o
gcc -I/home/jian/postgres/pg16/include/postgresql/server -fPIC -c funcs.c
gcc -shared -o funcs.so funcs.o
*/
#include "postgres.h"
#include "executor/executor.h"
#include "utils/numeric.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(c_overaid);
Datum
c_overaid(PG_FUNCTION_ARGS)
{
HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
Numeric limit = PG_GETARG_NUMERIC_COPY(1);
bool isnull;
Datum salary;
salary = GetAttributeByName(t,"salary",&isnull);
if(isnull)
PG_RETURN_BOOL(false);
/* alternatively, we might prefer to do PG_RETURN_NULL() for null salary */
PG_RETURN_BOOL(DatumGetNumericCopy(salary) > limit);
}
---------------------------------------------------------------------------
then quit the session and reconnect the session.
drop table emp cascade;
create table emp(empid bigint, salary numeric, name text);
insert into emp values(1,1000,'a1');
insert into emp values(2,1100,'a2');
insert into emp values(3,1200,'a3');
CREATE OR REPLACE FUNCTION c_overaid(emp, numeric) returns boolean AS '/home/jian/pg_ext/misc/funcs','c_overaid' LANGUAGE C STRICT;
select name, c_overaid(emp,numeric '1110') as over_paid from emp;
create table emp(empid bigint, salary numeric, name text);
insert into emp values(1,1000,'a1');
insert into emp values(2,1100,'a2');
insert into emp values(3,1200,'a3');
CREATE OR REPLACE FUNCTION c_overaid(emp, numeric) returns boolean AS '/home/jian/pg_ext/misc/funcs','c_overaid' LANGUAGE C STRICT;
select name, c_overaid(emp,numeric '1110') as over_paid from emp;
it returns
name | over_paid
------+-----------
a1 | t
a2 | t
a3 | t
(3 rows)
I also tried PG_GETARG_NUMERIC,DatumGetNumeric(salary).
Re: 38.10.6. Composite-Type Arguments C-language function code demo works for int, but not for numeric.
From
Tom Lane
Date:
jian he <jian.universality@gmail.com> writes:
> column "salary" int data type works fine. But it does not work if the
> "salary" column data type is numeric.
Your problem is that numeric is not a primitive C type:
> PG_RETURN_BOOL(DatumGetNumericCopy(salary) > limit);
That is comparing two pointers-to-numerics, not the values of
the numerics. You'd need to invoke numeric_cmp() if you
want a sensible result.
regards, tom lane