Re: making data types

> I am now wondering if someone could please explain to me how to make a new data
> type that I can use in my "C" functions and return to the calling PL/pgSQL
> function.

I may be well out of date on this, but I've been under impression that
you can not directly link to user functions form within PL/pgSQL.

Assuming that's true, all you can do is call functions registered with
postgres. That in turn means that your functions must operate on the
types known to postgres and also return known types or 'opaque'.

> for example in "C" I might have something like:
>
> typedef struct dataset
> {
> char one[20];
> char two[30];
> } dataset;
>
>
> then I could easily have:
>
> dataset *work_on_data_set(text *invalue1, int4 invalue2)
> {
> dataset *somedataset;
> .
> .
> .
> return somedataset;
> }
>
> My question is how to declare "dataset" so that both the "C" and PL/pgSQL can
> see the same structure (type)?

Assuming (again, I am not perfectly sure) that PL/pgSQL can only see
what the rest of the system sees, you need to declare a type. A
minimal useful declaration requires you to provide two methods for
input and output (as in Complex type example,
http://www.postgresql.org/users-lounge/docs/7.1/programmer/xtypes.html)

The reason for this is that the data exchange between your extension
and the postgres clients or external files you process with COPY
occurs via character strings. This means that your input method must
have a parser -- in the example you're contemplating, a very simple
one -- to split the input string and store individual values as your c
struct elements. The output method usually concatenates the values
into a formatted string or otherwise serializes your structure. In
addition, you can define access methods that will obtain individual
elements of your data type, but you won't be able to bypass the
parsing step and set elements individually, unless you synthesize the
new instance from other data types (it is possible, for example, to
construct a box using two points).

One should regard user-defined functions as filters acting on
individual instances of either built-in or user-defined types. I have
yet to see an actual user-defined function acting on multiple rows
(although the manual says that should as well be possible). Things the
user-defined functions can do are more or less limited to the
following:

1. Create an instance of a type from a string (e.g., complex_in(), point_in())

2. Return a string representing the instance when query
result is sent to the client (complex_out())

3. Obtain an individual data element or compute a function of the data
stored in instance of a type. For example, one could add two methods,
say, x() and y() to the complex type in order to obtain the real
coordinates of a complex value. box_center() is an example of the
function that both extracts and processes the data.

4. Construct an instance of a type from instances of more primitive
types (there are no existing examples I am aware of, but it is
possible define functions like create_box(point, point))

5. Convert between different types.

For more insight, check out the type-related stuff in contrib -- in
particular, contrib/isbn_issn, contrib/seg contrib/cube and
contrib/intarray (in the increasing order of complexity).

--Gene