Re: making data types
От | selkovjr@mcs.anl.gov |
---|---|
Тема | Re: making data types |
Дата | |
Msg-id | 200104162307.SAA01810@selkovjr.xnet.com обсуждение исходный текст |
Ответ на | making data types (Lonnie Cumberland <lonnie_cumberland@yahoo.com>) |
Список | pgsql-interfaces |
> 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
В списке pgsql-interfaces по дате отправления: