Dynamic/polymorphic record/composite return types for C user-defined-functions

Поиск
Список
Период
Сортировка
От Stephen Scheck
Тема Dynamic/polymorphic record/composite return types for C user-defined-functions
Дата
Msg-id CAKjnHz2hhkymKKpduskgPFPW+D+-TXvwjTEXuq+yRax0ZpOVdA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Dynamic/polymorphic record/composite return types for C user-defined-functions
Список pgsql-general
Hi,

I'm trying to write some user-defined functions in C which operate on a large object (so their common first argument will be an OID referencing an object in the pg_largeobject catalog table created with lo_create()) and return either a single row or a set depending on the function. Depending on the contents of the BLOB, some of the functions have a need to return polymorphic number column(s) as part of their result row (i.e. it could be an integer, real or double depending on the input BLOB).

I've tried various approaches for this but none of them quite work the way I need and I'm wondering if I'm missing a fundamental bit of understanding of Postgres' type system or it simply doesn't support what I want to do. Here's what I've tried:

1) Declare the function as RETURNS my_type (or RETURNS SETOF my_type), my_type having been defined with CREATE TYPE my_type AS ... with the column defined as a specific number type (integer, real, double precision, etc.). This works as I want, but only allows supporting the specific number type declared (since function signature polymorphism can only differentiate by input types, and any* types are not allowed in CREATE TYPE definitions).

2) Declare the function as RETURNS an anonymous row type, via OUT parameters or RETURNS TABLE. Declare the polymorphic number column as "anynonarray". The problem here is without a polymorphic IN parameter, the OUT type cannot be resolved. I worked around this by adding DEFAULT NULL::integer IN parameter which satisfies CREATE FUNCTION and calls but doesn't propagate the correct type through the FunctionCallInfo (I can't recall the exact error message but it didn't work).

3) Declare the function as RETURNS an anonymous row type, via OUT parameters or RETURNS TABLE. Declare the polymorphic number column as "any", which doesn't enforce correspondence between IN and OUT parameters. Doesn't work - when I call the function I get this: "ERROR: cannot display a value of type any". I don't think this would work even if the column isn't in the select-list (i.e. just used as a join or filter condition) since if I do an explicit cast, I get this error message: 'cannot cast type "any" to integer'.

As an aside, does this imply "any" as an OUT parameter has no use?

4) Declare the function as RETURNS RECORD or RETURNS SETOF RECORD. Use CreateTemplateTupleDesc()/BlessTupleDesc() to dynamically create a tuple description on the fly and return it. Depending on call context, I get different error messges:

SELECT * FROM info(<lo_oid>);
ERROR: a column definition list is required for functions returning "record"

-- or

SELECT (info(lo_oid_column)).* FROM test_table;
ERROR: record type has not been registered

I'm out of ideas. Isn't this the kind of dynamic behavior for which CreateTemplateTupleDesc()/BlessTupleDesc() is intended?

Any suggestions appreciated.

Cheers,
-Steve

В списке pgsql-general по дате отправления:

Предыдущее
От: Gavin Flower
Дата:
Сообщение: Re: Using varchar primary keys.
Следующее
От: Gavin Flower
Дата:
Сообщение: Re: Money casting too liberal?