1

My question is similar to this link. I have a table with millions of rows I have to put it into a single array sort it and get the min and max value and return the result as text.

eg. Table name: Sample,Column Name: id

The column id hold millions of records and the datatype may be integer, float etc.,

I have to RETURN the result as min-max (minimum value separator maximum value like 1->5)

Any help is appreciated. Thank You in Advance.

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/* Add a prototype marked PGDLLEXPORT */
PGDLLEXPORT Datum make_array(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(make_array);

Datum
make_array(PG_FUNCTION_ARGS)
{
ArrayType* result;
ArrayType* result1;
Oid         element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
Datum       element;
bool        isnull;
int16       typlen;
bool        typbyval;
char        typalign;
int         ndims;
int         dims[MAXDIM];
int         lbs[MAXDIM];

if (!OidIsValid(element_type))
    elog(ERROR, "could not determine data type of input");

/* get the provided element, being careful in case it's NULL */
isnull = PG_ARGISNULL(0);
if (isnull)
    element = (Datum)0;
else
    element = PG_GETARG_DATUM(0);

/* we have one dimension */
ndims = 1;
/* and one element */
dims[0] = 1;
/* and lower bound is 1 */
lbs[0] = 1;

/* get required info about the element type */
get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);

/* now build the array */
result = construct_md_array(&element, &isnull, ndims, dims, lbs,
    element_type, typlen, typbyval, typalign);

PG_RETURN_ARRAYTYPE_P(result);

}

The result after compiling this code is as follows

enter image description here

I am a newbie to Create Postgres C Extensions.

Improve this question
7
  • 1
    You should not do that with arrays. Arrays with millions of elements are unwieldy, and performance will be bad. Your requirement can easily be met with a small SQL function if you use the table and have an index on the column. Commented May 5, 2020 at 6:47
  • Hi Laurenz, I understand it can be done with a small SQL function and concat them to get the desired result like "Select min(id)||' _ '|| max(id) from table_name". But I would like to do it in C function. Is there any other way without using ARRAY to achieve this? Commented May 5, 2020 at 17:58
  • Your question starts with "I have to". So is that an exercise in C programming (no problem with that) or are you trying to solve a real problem? In the second case I don't understand the insistence on a solution like that. Commented May 6, 2020 at 6:43
  • Hi Laurenz, It is kind of an exercise. Is there any way to achieve this? Commented May 7, 2020 at 7:58
  • 1
    You should start by reading src/include/utils/array.h, it provides the functions for manipulating arrays. Commented May 7, 2020 at 8:36
Related questions
4
Postgres C Function - Passing & Returning Numerics
1
PostgreSQL module - How to map an array of parameters into the function?
1
Create simple C function for PostgreSQL with array input and array output
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.