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

From: Stephen Scheck <singularsyntax(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Dynamic/polymorphic record/composite return types for C user-defined-functions
Date: 2013-04-02 06:12:45
Message-ID: CAKjnHz2hhkymKKpduskgPFPW+D+-TXvwjTEXuq+yRax0ZpOVdA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Flower 2013-04-02 07:40:18 Re: Money casting too liberal?
Previous Message Gavin Flower 2013-04-02 05:54:52 Re: Using varchar primary keys.