Search code examples
cpostgresqlalgorithmhierarchical-clustering

How can I get table tuple and transform it into an array in C for postgres?

I'm following Postgres documentation https://www.postgresql.org/docs/8.2/xfunc-c.html for writing C function and creating the extension (for hierarchial clustering) and I'm confused.

  1. So I can get a tuple by using HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
  2. How can I get attribute values in this tuple? We have GET_ARGUMENT_BY_NUM, can I get a value for each column and put it into an array? (For some reason i want to get data from table and for example, clusterize it).
  3. There is an example of using specific table for a function (emp table). How can I use random table for my function (I couldn't find the example)?
  4. Is c_overpaid(emp, limit) (in documentation) called one time for emp table, or is it called as much as the rows in the table?
  5. for hierarchical-clustering: can I get table data from postgres, write it into a temp file, read that file, put it into array, clusterize it and put the result into database? (like create or alter table and do a partitioning? like this: hub - is whole table, part_1 is one cluster, part_2 is the second one etc)
Solution

  • You should read the documentation for the current version.

    1. Yes.

    2. As the example shows, with GetAttributeByName, but there is also a GetAttributeByNum function. I assume you are talking about a C array and not a PostgreSQL array. You can stuff all the values into an array, sure, if they have the same data type.

    3. Then you would have to use the special type record. For a code sample, look at the functions record_to_json and composite_to_json in src/backend/utils/adt/json.c.

    4. It is called for each row found, since it appears in the SELECT list.

    5. That's a bit vague, but sure. I don't see why you'd want to extract that from a table though. Why not write your own table access method, since it looks like you want to define a new way of storing tables.

      But be warned, that would be decidedly non-trivial, and you'd better first get your feet wet with more mundane stuff.