Thread: Function proposal to find the type of a datum
Hi all, I found the need to determine the type of a datum at runtime. David Fetter and Elein have already written about this: http://www.varlena.com/varlena/GeneralBits/117.php (My scenario is similar to the article there; I was writing a procedure which unit-tests other procedures. It needs to know the type of arguments passed so that when constructing queries, it may avoid quoting (for example) integers, so that the appropiate function is selected for those which are overloaded. As I did not want to assume that a user has permission to create PL functions in C, I ended up casting to an integer and catching exceptions to determine failure!) I think this would be a useful function to expose by default. David suggested pg_catalog.pg_type_of(ANYELEMENT). If this is viewed as a good idea, I'd like to try to implement this. Suggestions welcome, -- Kate
Hello, you can identify type via operator OF. like: IF a IS OF INTEGER THEN RAISE NOTICE 'Parametr a is numeric'; ELSIF a IS OF varchar THEN RAISE NOTICE 'Parametr a isstring'; END IF; Regards Pavel Stehule _________________________________________________________________ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> you can identify type via operator OF.
> IF a IS OF INTEGER THEN
> RAISE NOTICE 'Parametr a is numeric';
Yeah, that is the SQL-standard syntax, but I think our implementation
doesn't work the way Kate would like: if "a" is an ANYELEMENT function
parameter then (I think) only IS OF ANYELEMENT would succeed. Would it
conform to the SQL spec for the test to "look through" ANYELEMENT to the
actual parameter type? Would it be reasonably implementable to do that?
regards, tom lane
On Fri, Feb/ 2/07 02:17:51AM -0500, Tom Lane wrote: > "Pavel Stehule" <pavel.stehule@hotmail.com> writes: > > you can identify type via operator OF. > > > IF a IS OF INTEGER THEN > > RAISE NOTICE 'Parametr a is numeric'; > > Yeah, that is the SQL-standard syntax, but I think our implementation > doesn't work the way Kate would like: if "a" is an ANYELEMENT function > parameter then (I think) only IS OF ANYELEMENT would succeed. Would it > conform to the SQL spec for the test to "look through" ANYELEMENT to the > actual parameter type? Would it be reasonably implementable to do that? Well, actually, I discussed this on IRC with Pavel, and for the above I have syntax error at or near "INTEGER". My guess was that the grammar changed as an unintentional side-effect of some other change. He corrected his suggestion: OF takes a list of types; it needs parentheses: IF a IS OF (INTEGER) THEN This operator would look wonderful for my particular needs, were it not that it has completely different semantics! It appears to test to see what a datum actually *is* (in my case TEXT), rather than what it may be parsed as (in my case, INTEGER). Meanwhile, I still think the function David proposed is a worthy addition (and I still have a user-case for it!), as using just the OF operator for something similar, one would have to explictly test against every type required. Thank you, -- Kate
Kate F <kate@cats.meow.at> writes:
> ... OF takes a list of types; it needs parentheses:
> IF a IS OF (INTEGER) THEN
Oh, right, minor detail.
> Meanwhile, I still think the function David proposed is a worthy
> addition (and I still have a user-case for it!), as using just the OF
> operator for something similar, one would have to explictly test
> against every type required.
Um, but what would you do differently with an OID result? The nice
thing about the SQL-spec syntax is that it avoids depending on anything
so implementation-specific as a pg_type OID. So if it covers the
territory then I'd be inclined to go that way. What useful
functionality does it lack?
regards, tom lane
On Fri, Feb/ 2/07 02:41:15AM -0500, Tom Lane wrote: > > > Meanwhile, I still think the function David proposed is a worthy > > addition (and I still have a user-case for it!), as using just the OF > > operator for something similar, one would have to explictly test > > against every type required. > > Um, but what would you do differently with an OID result? The nice > thing about the SQL-spec syntax is that it avoids depending on anything > so implementation-specific as a pg_type OID. So if it covers the > territory then I'd be inclined to go that way. What useful > functionality does it lack? There are two differences here. I did try to explain these in my previous mail, but perhaps I wasn't clear enough. Firstly: Substitute the name of the type (as TEXT) if you think that's more appropiate. The difference between OF and this function is that this function is pulling the type from the datum, rather than explicitly testing it against types the user suggests. If I wanted to find the type of x using OF, I would have to check it for all types which interest me: IF a IS OF (INTEGER) THENt := 'INTEGER'; ELSE IF a IS OF (TEXT) THENt := 'TEXT'; ELSE IF a IS OF (REAL) THENt := 'REAL'; ... and so on. Versus: t := pg_type_of(a); Secondly, the semantics are different: OF yields the type the datum currently is; pg_type_of() (perhaps it should be renamed?) returns the most appropiate type to which the datum may be cast, if I understand get_fn_expr_argtype() correctly. Regards, -- Kate
Kate F <kate@cats.meow.at> writes:
> The difference between OF and this function is that this function is
> pulling the type from the datum, rather than explicitly testing it
> against types the user suggests. If I wanted to find the type of x
> using OF, I would have to check it for all types which interest me:
> IF a IS OF (INTEGER) THEN
> t := 'INTEGER';
> ELSE IF a IS OF (TEXT) THEN
> t := 'TEXT';
> ELSE IF a IS OF (REAL) THEN
> t := 'REAL';
> ...
> and so on. Versus:
> t := pg_type_of(a);
Well, but what are you going to do with "t" after that? AFAICS the
next step is going to be something like
IF t = 'integer'::regtype THEN ...
ELSE IF t = 'text'::regtype THEN ...
etc etc
So it seems to me that this is functionally about the same, except that
it exposes two implementation-dependent concepts (pg_type OIDs and
regtype) where the first exposes neither.
Your approach would help if there were a reason to pass "t" as a
variable to someplace not having access to "a", but I don't see a
very compelling use-case for that.
> Secondly, the semantics are different: OF yields the type the datum
> currently is; pg_type_of() (perhaps it should be renamed?) returns the
> most appropiate type to which the datum may be cast, if I understand
> get_fn_expr_argtype() correctly.
You don't, I think --- there's really no such thing as a "datum of type
ANYELEMENT", real datums always have some more-specific type. But my
question upthread was directed exactly to the point of how we should
interpret IS OF applied to a polymorphic function argument. It's at
least possible to argue that it's OK to interpret it the way you need.
regards, tom lane
On Fri, Feb/ 2/07 03:06:19AM -0500, Tom Lane wrote:
> Kate F <kate@cats.meow.at> writes:
> > The difference between OF and this function is that this function is
> > pulling the type from the datum, rather than explicitly testing it
> > against types the user suggests. If I wanted to find the type of x
> > using OF, I would have to check it for all types which interest me:
>
> > IF a IS OF (INTEGER) THEN
> > t := 'INTEGER';
> > ELSE IF a IS OF (TEXT) THEN
> > t := 'TEXT';
> > ELSE IF a IS OF (REAL) THEN
> > t := 'REAL';
> > ...
> > and so on. Versus:
>
> > t := pg_type_of(a);
>
> Well, but what are you going to do with "t" after that? AFAICS the
> next step is going to be something like
>
> IF t = 'integer'::regtype THEN
> ...
> ELSE IF t = 'text'::regtype THEN
> ...
> etc etc
I don't follow that cast at all, I'm afraid. I wasn't intending to
have a set of IF..ELSE IF statements like that, though - see below.
> So it seems to me that this is functionally about the same, except that
> it exposes two implementation-dependent concepts (pg_type OIDs and
> regtype) where the first exposes neither.
>
> Your approach would help if there were a reason to pass "t" as a
> variable to someplace not having access to "a", but I don't see a
> very compelling use-case for that.
In my case, I am constructing a query (to be exexecuted dynamically)
wherein I pass along some of the arguments I am given. This query calls
a function specified by an argument passed to me. If that function is
overloaded, I need to be able to cast its arguments to appropiate
types so that PostgreSQL may decide which function of that name to
call. I'm sure there must be other uses, (or is this an unneccessary
feature?).
For the moment, I'm only using this information to see if I need to
quote a parameter or not, but I suspect my function will trip up when
told to execute something that is overloaded in a more complex way.
Of course, I don't want to expose anything unneccessarily.
> > Secondly, the semantics are different: OF yields the type the datum
> > currently is; pg_type_of() (perhaps it should be renamed?) returns the
> > most appropiate type to which the datum may be cast, if I understand
> > get_fn_expr_argtype() correctly.
>
> You don't, I think --- there's really no such thing as a "datum of type
> ANYELEMENT", real datums always have some more-specific type. But my
> question upthread was directed exactly to the point of how we should
> interpret IS OF applied to a polymorphic function argument. It's at
> least possible to argue that it's OK to interpret it the way you need.
I've no suggestion to make on whether IS OF should look inside
ANYELEMENT or not.
This is quite past my knowledge of PostgreSQL... If I understand you
correctly, ANYELEMENT is unrelated to my suggestion.
I see my misunderstanding: '2' IS OF (INTEGER) yields false: fine.
However I was expecting that pg_type_of('2') would return 'INTEGER': it
wouldn't, of course. So, I understand you here: there would be no
difference between this and IS OF in the way I had imagined.
That still leaves the difference in usage I mention above. Does that
sound sane?
Thank you,
--
Kate
Kate F wrote:
> I see my misunderstanding: '2' IS OF (INTEGER) yields false: fine.
> However I was expecting that pg_type_of('2') would return 'INTEGER': it
> wouldn't, of course. So, I understand you here: there would be no
> difference between this and IS OF in the way I had imagined.
It's not even possible to have a function that determines the type of a
value given that we have overlapping types. How do you know that "2"
isn't an int8 rather than int4, or numeric, or just text. What about
'now'? That's a valid timestamp as well as text.
Now, if we had an can_be_cast_to(TEXT-VAL,TYPE) that would at least let
you check against a pre-determined list of types. The only way I know of
at present is to trap an exception if it fails.
I think you're going to have to store your arguments with their types.
-- Richard Huxton Archonet Ltd
Kate F <kate@cats.meow.at> writes:
> In my case, I am constructing a query (to be exexecuted dynamically)
> wherein I pass along some of the arguments I am given. This query calls
> a function specified by an argument passed to me. If that function is
> overloaded, I need to be able to cast its arguments to appropiate
> types so that PostgreSQL may decide which function of that name to
> call. I'm sure there must be other uses, (or is this an unneccessary
> feature?).
> For the moment, I'm only using this information to see if I need to
> quote a parameter or not, but I suspect my function will trip up when
> told to execute something that is overloaded in a more complex way.
Hmmm. Actually, I think you'd be best off not to think in terms of
"quote or not", but instead always quote and cast. You're going to be
building up strings to EXECUTE, right? ISTM what you want is something
like
... || quote_literal(aparam::text) || '::' || type_name_of(aparam) || ...
where type_name_of is something that produces the type name as a string,
not directly its OID. So one way to counter the "it's exposing internal
concepts" gripe is to not expose the OID at all just the type name.
Even if the raw function did return the OID you'd need a wrapper to
convert to a string name.
The other problem here is that I've blithely assumed that you can cast
anything to text; you can't. Now in plpgsql you can work around that
because plpgsql will cast anything to anything via textual intermediate
form, so you could hack it with
texttmp := aparam; ... || quote_literal(texttmp) || '::' || type_name_of(aparam) || ...
There's been talk off and on of allowing an explicit cast to and from
text throughout the system rather than just in plpgsql, but I dunno if
you want to fight that battle today.
regards, tom lane
On Fri, Feb/ 2/07 10:09:24AM +0000, Richard Huxton wrote:
> Kate F wrote:
> >I see my misunderstanding: '2' IS OF (INTEGER) yields false: fine.
> >However I was expecting that pg_type_of('2') would return 'INTEGER': it
> >wouldn't, of course. So, I understand you here: there would be no
> >difference between this and IS OF in the way I had imagined.
>
> It's not even possible to have a function that determines the type of a
> value given that we have overlapping types. How do you know that "2"
> isn't an int8 rather than int4, or numeric, or just text. What about
> 'now'? That's a valid timestamp as well as text.
Yes, quite. I understand this; I'd just misunderstood what
get_fn_expr_argtype() did.
> Now, if we had an can_be_cast_to(TEXT-VAL,TYPE) that would at least let
> you check against a pre-determined list of types. The only way I know of
> at present is to trap an exception if it fails.
That's exactly what I'm doing, currently.
> I think you're going to have to store your arguments with their types.
I may do!
Regards,
--
Kate
On Fri, Feb/ 2/07 09:52:08AM -0500, Tom Lane wrote: > Kate F <kate@cats.meow.at> writes: > > In my case, I am constructing a query (to be exexecuted dynamically) > > wherein I pass along some of the arguments I am given. This query calls > > a function specified by an argument passed to me. If that function is > > overloaded, I need to be able to cast its arguments to appropiate > > types so that PostgreSQL may decide which function of that name to > > call. I'm sure there must be other uses, (or is this an unneccessary > > feature?). > > For the moment, I'm only using this information to see if I need to > > quote a parameter or not, but I suspect my function will trip up when > > told to execute something that is overloaded in a more complex way. > > Hmmm. Actually, I think you'd be best off not to think in terms of > "quote or not", but instead always quote and cast. You're going to be > building up strings to EXECUTE, right? ISTM what you want is something > like > > ... || quote_literal(aparam::text) || '::' || type_name_of(aparam) || ... > > where type_name_of is something that produces the type name as a string, > not directly its OID. So one way to counter the "it's exposing internal > concepts" gripe is to not expose the OID at all just the type name. That's precisely what I suggested a moment ago! This is what I'm proposing is added. (And whatever the decision regarding ANYELEMENT of, I believe this should behave the same as IS OF) > Even if the raw function did return the OID you'd need a wrapper to > convert to a string name. This is what David did in his article. > The other problem here is that I've blithely assumed that you can cast > anything to text; you can't. Now in plpgsql you can work around that > because plpgsql will cast anything to anything via textual intermediate > form, so you could hack it with > > texttmp := aparam; > ... || quote_literal(texttmp) || '::' || type_name_of(aparam) || ... That's interesting - I didn't realise that not everything could be cast to text. > There's been talk off and on of allowing an explicit cast to and from > text throughout the system rather than just in plpgsql, but I dunno if > you want to fight that battle today. I'm sticking to things I could possibly implement :) Thank you, -- Kate
Kate F <kate@cats.meow.at> writes:
> (And whatever the decision regarding ANYELEMENT of, I believe this
> should behave the same as IS OF)
In the light of morning I think it may be a non-problem. The way that a
plpgsql function with an ANYELEMENT parameter really works is that on
first invocation with a parameter of a specific type, we generate a new
parse-tree on the fly with the parameter being taken as of that type.
So the IS OF or equivalent operation would never see ANYELEMENT, and
there's nothing to "look through". (You might check this by seeing if
IS OF behaves sanely, before you go and spend time on a type_of function
...)
regards, tom lane
On Fri, Feb/ 2/07 11:17:46AM -0500, Tom Lane wrote: > Kate F <kate@cats.meow.at> writes: > > (And whatever the decision regarding ANYELEMENT of, I believe this > > should behave the same as IS OF) > > In the light of morning I think it may be a non-problem. The way that a > plpgsql function with an ANYELEMENT parameter really works is that on > first invocation with a parameter of a specific type, we generate a new > parse-tree on the fly with the parameter being taken as of that type. > So the IS OF or equivalent operation would never see ANYELEMENT, and > there's nothing to "look through". (You might check this by seeing if > IS OF behaves sanely, before you go and spend time on a type_of function > ...) I have checked this - I mentioned earlier, when I spoke about my discussion on IRC with Pavel, but had since forgotten! IS OF for an array of TEXT yields TEXT. I think this is convenient behaviour (likewise for the function I'm proposing). So, to conclude, we still have a valid use-case (which you explained a little more explicitly than I did). Shall I attempt to implement it? (that is, type_name_of() which returns TEXT) Regards, -- Kate
Kate F <kate@cats.meow.at> writes:
> So, to conclude, we still have a valid use-case (which you explained a
> little more explicitly than I did). Shall I attempt to implement it?
> (that is, type_name_of() which returns TEXT)
I think I'd suggest pg_type_name ... or maybe pg_type_name_of ...
also, the code you need to convert OID to name is already there,
see regtypeout.
regards, tom lane
On Fri, Feb/ 2/07 11:37:13AM -0500, Tom Lane wrote: > Kate F <kate@cats.meow.at> writes: > > So, to conclude, we still have a valid use-case (which you explained a > > little more explicitly than I did). Shall I attempt to implement it? > > (that is, type_name_of() which returns TEXT) > > I think I'd suggest pg_type_name ... or maybe pg_type_name_of ... > also, the code you need to convert OID to name is already there, > see regtypeout. Fantastic! Thank you for the interesting discussion, -- Kate