Thread: RfD: more powerful "any" types
Hi, I'm seeking more powerful "any" pseudotypes. In particular, consider a function foo(type1, type2) returns type3 where type1 and type2 can be both any element type, but not necessarily both of the same type. Currently this cannot be made to work: you could try to write the function this way: foo(anyelement, anyelement) returns anyelement but this will force them to be both of the same type, which is not what we want. In my opinion this is a missing feature of our type system. One seemingly trivial idea would be to have anyelement2, anyelement3, and so on. This is not a very good solution, because we'd have to fill the catalogs with a large bunch of new pseudotypes, and the code with a bunch of hardcoded tests -- and there are already 27 cases of ANYELEMENTOID in our code. For a practical example, I am trying to write a function that returns how many NULL arguments it has (this is useful in table CHECK constraints). One simple idea is to use type "unknown": CREATE FUNCTION a (unknown, unknown, unknown) RETURNS INT LANGUAGE plpgsql AS $$ DECLARE count int = 0; BEGIN IF $1 IS NULL THEN count = count + 1; END IF; IF $2 IS NULL THEN count = count + 1; END IF; if $3 IS NULL THEN count= count + 1; END IF; RETURN count; END $$; The problem is that it doesn't work. This trivial query does: alvherre=# select a(null, '2', null);a ---2 (1 fila) But this one does not: alvherre=# select a(null, 2, null); ERROR: function a(unknown, integer, unknown) does not exist and you cannot cast the integer: alvherre=# select a(null, 2::unknown, null); ERROR: cannot cast type integer to unknown Before I spend time trying to figure out how this works, 1. is there agreement that this is a problem and needs fixed, and 2. does anybody have an idea how to attack it? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Sep 8, 2009, at 9:12 AM, Alvaro Herrera wrote: > foo(anyelement, anyelement) returns anyelement > > but this will force them to be both of the same type, which is not > what > we want. In my opinion this is a missing feature of our type system. Oh yes, agreed. I've run into this with pgTAP many times. > One seemingly trivial idea would be to have anyelement2, anyelement3, > and so on. This is not a very good solution, because we'd have to > fill > the catalogs with a large bunch of new pseudotypes, and the code > with a > bunch of hardcoded tests -- and there are already 27 cases of > ANYELEMENTOID in our code. Yes, and foo() might be called with two different data types, or two of the same, and both should work. <snip what="other useful information" /> > Before I spend time trying to figure out how this works, > 1. is there agreement that this is a problem and needs fixed, and +1 > 2. does anybody have an idea how to attack it? Why can't anyelement be freed from this constraint? Best, David
David E. Wheeler wrote: > On Sep 8, 2009, at 9:12 AM, Alvaro Herrera wrote: > >2. does anybody have an idea how to attack it? > > Why can't anyelement be freed from this constraint? Because it would break other uses of it, I think. IIRC the original use of anyelement was that it would resolve to the element type of an anyarray argument-or-return type. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> I'm seeking more powerful "any" pseudotypes.
If you don't want any constraints at all on the argument types, you
can use "any" (with the quotes, because it's a reserved word :-().
If you want some constraints but not "anyelement", please be more
specific about what you want.
regards, tom lane
On Sep 8, 2009, at 9:23 AM, Alvaro Herrera wrote: >>> 2. does anybody have an idea how to attack it? >> >> Why can't anyelement be freed from this constraint? > > Because it would break other uses of it, I think. IIRC the original > use > of anyelement was that it would resolve to the element type of an > anyarray argument-or-return type. Does it still? Need it? David
On Sep 8, 2009, at 9:25 AM, Tom Lane wrote: > If you don't want any constraints at all on the argument types, you > can use "any" (with the quotes, because it's a reserved word :-(). > If you want some constraints but not "anyelement", please be more > specific about what you want. Oooh, I need to try that. How far back does that behavior go, compatibility-wise? Best, David
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > I'm seeking more powerful "any" pseudotypes. > > If you don't want any constraints at all on the argument types, you > can use "any" (with the quotes, because it's a reserved word :-(). > If you want some constraints but not "anyelement", please be more > specific about what you want. That would work, except that plpgsql and SQL don't like it: ERROR: PL/pgSQL functions cannot accept type "any" ERROR: SQL functions cannot have arguments of type "any" -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sep 8, 2009, at 9:32 AM, Alvaro Herrera wrote: > That would work, except that plpgsql and SQL don't like it: > > ERROR: PL/pgSQL functions cannot accept type "any" > ERROR: SQL functions cannot have arguments of type "any" Seems to go for other PLs, as well: ERROR: PL/Perl functions cannot accept type "any" Perhaps that could be changed? Best, David
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> If you don't want any constraints at all on the argument types, you
>> can use "any" (with the quotes, because it's a reserved word :-().
> That would work, except that plpgsql and SQL don't like it:
Intentionally so, because there's not a whole lot you can *do* with an
ANY parameter, other than checking it for null. Perhaps the real
question is about what semantics you're expecting for these
unconstrained parameters.
regards, tom lane
On Sep 8, 2009, at 9:57 AM, Tom Lane wrote:
> Intentionally so, because there's not a whole lot you can *do* with an
> ANY parameter, other than checking it for null. Perhaps the real
> question is about what semantics you're expecting for these
> unconstrained parameters.
For my purposes, I guess implicit casting for comparing values, as in
arg_a IS DISTINCT FROM arg_b
and
arg_a = arg_b
or
arg_a <> arg_b
It'd work if there was a way to cast one to the type of the other,
such as comparing a TEXT to a VARCHAR. But maybe that'd be too magical…
Best,
David
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 8, 2009, at 9:57 AM, Tom Lane wrote:
>> Intentionally so, because there's not a whole lot you can *do* with an
>> ANY parameter, other than checking it for null. Perhaps the real
>> question is about what semantics you're expecting for these
>> unconstrained parameters.
> For my purposes, I guess implicit casting for comparing values, as in
> arg_a IS DISTINCT FROM arg_b
Surely you'd want arg_a and arg_b constrained to the same type,
otherwise there is no certainty that that means anything at all.
regards, tom lane
On Tue, Sep 8, 2009 at 12:12 PM, Alvaro Herrera<alvherre@commandprompt.com> wrote: > Hi, > > I'm seeking more powerful "any" pseudotypes. In particular, consider a > function > > foo(type1, type2) returns type3 > > where type1 and type2 can be both any element type, but not necessarily > both of the same type. Currently this cannot be made to work: you could > try to write the function this way: > > foo(anyelement, anyelement) returns anyelement > > but this will force them to be both of the same type, which is not what > we want. In my opinion this is a missing feature of our type system. > > One seemingly trivial idea would be to have anyelement2, anyelement3, > and so on. This is not a very good solution, because we'd have to fill > the catalogs with a large bunch of new pseudotypes, and the code with a > bunch of hardcoded tests -- and there are already 27 cases of > ANYELEMENTOID in our code. > > > For a practical example, I am trying to write a function that returns > how many NULL arguments it has (this is useful in table CHECK > constraints). One simple idea is to use type "unknown": > > CREATE FUNCTION a (unknown, unknown, unknown) RETURNS INT LANGUAGE plpgsql AS $$ > DECLARE > count int = 0; > BEGIN > IF $1 IS NULL THEN count = count + 1; END IF; > IF $2 IS NULL THEN count = count + 1; END IF; > if $3 IS NULL THEN count = count + 1; END IF; > RETURN count; > END $$; > > The problem is that it doesn't work. This trivial query does: > > alvherre=# select a(null, '2', null); > a > --- > 2 > (1 fila) > > But this one does not: > > alvherre=# select a(null, 2, null); > ERROR: function a(unknown, integer, unknown) does not exist > > and you cannot cast the integer: > alvherre=# select a(null, 2::unknown, null); > ERROR: cannot cast type integer to unknown > > > Before I spend time trying to figure out how this works, > 1. is there agreement that this is a problem and needs fixed, and > 2. does anybody have an idea how to attack it? Since you can do all these things and more in C functions, this becomes a right tool/wrong tool problem? plpgsql would be fairly hopeless without some reflection capabilities that we don't currently have...especially if you consider variadic functions which would be hard to reconcile with any behavior changes. I think if you continue going down this road you would end up with a type system along the lines with c++ templates...so you could do: DECLARE foo arg1%type; etc And maybe have unique generated plans for each unique set of supplied input types. merlin
On Sep 8, 2009, at 10:15 AM, Tom Lane wrote: >> arg_a IS DISTINCT FROM arg_b > > Surely you'd want arg_a and arg_b constrained to the same type, > otherwise there is no certainty that that means anything at all. Yes, for the purposes of pgTAP perhaps so. Then it's on the user to do the cast, because she decides that the cast is appropriate. Otherwise, as I said, perhaps it'd be too magical. Best, David
Alvaro Herrera <alvherre@commandprompt.com> writes:
> David E. Wheeler wrote:
>> Why can't anyelement be freed from this constraint?
> Because it would break other uses of it, I think.
Specifically, what are you going to do with something like
make_array(anyelement, anyelement) returns anyarray
There's no principled way to determine what anyarray means if the
arguments are not the same type.
I have no objection to adding some other pseudotype with different
behavior, but breaking anyelement is not the path.
regards, tom lane
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 8, 2009, at 9:25 AM, Tom Lane wrote:
>> If you don't want any constraints at all on the argument types, you
>> can use "any" (with the quotes, because it's a reserved word :-().
> Oooh, I need to try that. How far back does that behavior go,
> compatibility-wise?
Further than "anyelement", I think, or at least the same distance.
IIRC it was one of the types we split up "opaque" into.
regards, tom lane
On Tue, Sep 08, 2009 at 12:12:10PM -0400, Alvaro Herrera wrote: > Hi, > > I'm seeking more powerful "any" pseudotypes. In particular, > consider a function > > foo(type1, type2) returns type3 > > where type1 and type2 can be both any element type, but not > necessarily both of the same type. Currently this cannot be made to > work: you could try to write the function this way: > > foo(anyelement, anyelement) returns anyelement I'd like to see pseudo-types like ANYNUMERIC, and allow it to take an array decorator, which would really help for math-ish functions. Not sure where that fits in this discussion. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, 2009-09-08 at 10:23 -0700, David E. Wheeler wrote: > On Sep 8, 2009, at 10:15 AM, Tom Lane wrote: > > >> arg_a IS DISTINCT FROM arg_b > > > > Surely you'd want arg_a and arg_b constrained to the same type, > > otherwise there is no certainty that that means anything at all. > > Yes, for the purposes of pgTAP perhaps so. Then it's on the user to do > the cast, because she decides that the cast is appropriate. Otherwise, > as I said, perhaps it'd be too magical. That's beginning to sound a bit like a generics feature. E.g., CREATE FUNCTION the_same<T>(arg_a T, arg_b T) RETURNS bool AS $$ SELECT arg_a IS DISTINCT FROM arg_b; $$;
Peter Eisentraut wrote: > On Tue, 2009-09-08 at 10:23 -0700, David E. Wheeler wrote: > > On Sep 8, 2009, at 10:15 AM, Tom Lane wrote: > > > > >> arg_a IS DISTINCT FROM arg_b > > > > > > Surely you'd want arg_a and arg_b constrained to the same type, > > > otherwise there is no certainty that that means anything at all. > > > > Yes, for the purposes of pgTAP perhaps so. Then it's on the user to do > > the cast, because she decides that the cast is appropriate. Otherwise, > > as I said, perhaps it'd be too magical. > > That's beginning to sound a bit like a generics feature. E.g., > > CREATE FUNCTION the_same<T>(arg_a T, arg_b T) RETURNS bool AS $$ > SELECT arg_a IS DISTINCT FROM arg_b; > $$; Well, you can write that one with anyelement already. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, 2009-09-09 at 07:47 -0400, Alvaro Herrera wrote: > Peter Eisentraut wrote: > > On Tue, 2009-09-08 at 10:23 -0700, David E. Wheeler wrote: > > > On Sep 8, 2009, at 10:15 AM, Tom Lane wrote: > > > > > > >> arg_a IS DISTINCT FROM arg_b > > > > > > > > Surely you'd want arg_a and arg_b constrained to the same type, > > > > otherwise there is no certainty that that means anything at all. > > > > > > Yes, for the purposes of pgTAP perhaps so. Then it's on the user to do > > > the cast, because she decides that the cast is appropriate. Otherwise, > > > as I said, perhaps it'd be too magical. > > > > That's beginning to sound a bit like a generics feature. E.g., > > > > CREATE FUNCTION the_same<T>(arg_a T, arg_b T) RETURNS bool AS $$ > > SELECT arg_a IS DISTINCT FROM arg_b; > > $$; > > Well, you can write that one with anyelement already. Well, so far we've only seen use cases in this thread that either already work or that are not well-defined. ;-)
Peter Eisentraut <peter_e@gmx.net> writes:
> Well, so far we've only seen use cases in this thread that either
> already work or that are not well-defined. ;-)
Well, yeah, the question is can we extract a clear TODO item here.
I think there are two somewhat orthogonal issues:
1. Is a completely unconstrained argument type (ie "any") of any real
use to PL functions, and if so how can we expose that usefulness?
The only clear thing to do with such an argument is IS NULL/IS NOT NULL
tests, which might or might not be worth the trouble.
2. Is there any use for arguments with type constraints not covered
by the existing ANYFOO rules, and if so what do we add for that?
One comment on point 2 is that it was foreseen from the beginning
that there would be need for ANYELEMENT2 etc, and I'm actually rather
surprised that we've gone this long without adding them. Alvaro made
a good point about not wanting to multiply the various hard-wired
OID references, but perhaps some judicious code refactoring could
prevent a notational disaster.
regards, tom lane
2009/9/9 Tom Lane <tgl@sss.pgh.pa.us>: > Peter Eisentraut <peter_e@gmx.net> writes: >> Well, so far we've only seen use cases in this thread that either >> already work or that are not well-defined. ;-) > > Well, yeah, the question is can we extract a clear TODO item here. > > I think there are two somewhat orthogonal issues: > > 1. Is a completely unconstrained argument type (ie "any") of any real > use to PL functions, and if so how can we expose that usefulness? > The only clear thing to do with such an argument is IS NULL/IS NOT NULL > tests, which might or might not be worth the trouble. > > 2. Is there any use for arguments with type constraints not covered > by the existing ANYFOO rules, and if so what do we add for that? > > One comment on point 2 is that it was foreseen from the beginning > that there would be need for ANYELEMENT2 etc, and I'm actually rather > surprised that we've gone this long without adding them. Alvaro made > a good point about not wanting to multiply the various hard-wired > OID references, but perhaps some judicious code refactoring could > prevent a notational disaster. I hope so 2 independent polymorphic types are enough. The bigger problem is coexistence 'unknown' type and anyelement type. Simply we cannot to write equivalent coalesce, nullif functions vith only polymorphic types, because we cannot to force implicit casting unknown->text. maybe we could to add new function hint "unknown to some" CREATE OR REPLACE FUNCTION coalesce(VARIADIC anyelement[]) RETURNS anyelement AS $$ SELECT $1[i] FROM generate_subscripts($1) g(i) WHERE $1[i] IS NOT NULL $$ LANGUAGE sql UNKNOWN IS text; ??? Regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Sep 9, 2009, at 6:39 AM, Tom Lane wrote: > 1. Is a completely unconstrained argument type (ie "any") of any real > use to PL functions, and if so how can we expose that usefulness? > The only clear thing to do with such an argument is IS NULL/IS NOT > NULL > tests, which might or might not be worth the trouble. If you can pass an "any" to pg_typeof(), it's possible for functions to determine the types of arguments themselves and then to decide what to do with them (cast, etc.). I can see no reason not to give this ability to function authors, can you? > 2. Is there any use for arguments with type constraints not covered > by the existing ANYFOO rules, and if so what do we add for that? > > One comment on point 2 is that it was foreseen from the beginning > that there would be need for ANYELEMENT2 etc, and I'm actually rather > surprised that we've gone this long without adding them. Alvaro made > a good point about not wanting to multiply the various hard-wired > OID references, but perhaps some judicious code refactoring could > prevent a notational disaster. The difference between allowing ANYELEMENT2, ANYELEMENT3, ANYELEMENT . ++$i and allowing "any" escapes me. Best, David
On Sep 9, 2009, at 8:39 AM, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: >> Well, so far we've only seen use cases in this thread that either >> already work or that are not well-defined. ;-) > > Well, yeah, the question is can we extract a clear TODO item here. > > I think there are two somewhat orthogonal issues: > > 1. Is a completely unconstrained argument type (ie "any") of any real > use to PL functions, and if so how can we expose that usefulness? > The only clear thing to do with such an argument is IS NULL/IS NOT > NULL > tests, which might or might not be worth the trouble. Part of that should be providing a means to determine what the underlying type of an "any" is. Having that would allow functions to take actions appropriate to different types. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 9, 2009, at 6:39 AM, Tom Lane wrote:
>> 1. Is a completely unconstrained argument type (ie "any") of any real
>> use to PL functions, and if so how can we expose that usefulness?
>> The only clear thing to do with such an argument is IS NULL/IS NOT
>> NULL tests, which might or might not be worth the trouble.
> If you can pass an "any" to pg_typeof(), it's possible for functions
> to determine the types of arguments themselves and then to decide what
> to do with them (cast, etc.). I can see no reason not to give this
> ability to function authors, can you?
Well, yeah: it looks like a fertile source of security holes, not to
mention implementation difficulties (plpgsql really wants well-typed
expressions...). What you can do at the C level is not necessarily
sane to give to PL authors. I'm willing to consider a carefully spec'd
out proposal in this area, but "open the floodgates" ain't it.
> The difference between allowing ANYELEMENT2, ANYELEMENT3, ANYELEMENT .
> ++$i and allowing "any" escapes me.
In an example like
create function foo (anyelement, anyelement2, anyelement2)returns anyarray2
the second and third arguments would be tied to be of the same type,
and the result would be an array of that type; whereas the first
argument's type is unrelated. "any" doesn't give you any inter-argument
constraints nor any way to define the result type in terms of the
argument types. For a possibly realistic example, consider a function
defined as "locate the element of an array that equals the search
argument, and return the corresponding element of a second array, which
is possibly of a different type". This could be defined as
create function search_array (val anyelement, search_array anyarray,
result_array anyarray2)returns anyelement2
but "any" isn't nearly expressive enough.
regards, tom lane
On Sep 9, 2009, at 10:04 AM, Tom Lane wrote: > Well, yeah: it looks like a fertile source of security holes, not to > mention implementation difficulties (plpgsql really wants well-typed > expressions...). What you can do at the C level is not necessarily > sane to give to PL authors. I'm willing to consider a carefully > spec'd > out proposal in this area, but "open the floodgates" ain't it. Security holes? Huh? What security holes would there be that you don't already have with anyelement? >> The difference between allowing ANYELEMENT2, ANYELEMENT3, >> ANYELEMENT . >> ++$i and allowing "any" escapes me. > > In an example like > > create function foo (anyelement, anyelement2, anyelement2) > returns anyarray2 > > the second and third arguments would be tied to be of the same type, > and the result would be an array of that type; whereas the first > argument's type is unrelated. "any" doesn't give you any inter- > argument > constraints nor any way to define the result type in terms of the > argument types. For a possibly realistic example, consider a function > defined as "locate the element of an array that equals the search > argument, and return the corresponding element of a second array, > which > is possibly of a different type". This could be defined as > > create function search_array (val anyelement, > search_array anyarray, > result_array anyarray2) > returns anyelement2 > > but "any" isn't nearly expressive enough. I see. Yes, that is nice. Thanks for the examples. Best, David
Tom Lane wrote: > In an example like > > create function foo (anyelement, anyelement2, anyelement2) > returns anyarray2 > > the second and third arguments would be tied to be of the same type, > and the result would be an array of that type; whereas the first > argument's type is unrelated. Another possible example is sprintf: create function sprintf(text, anyelement, anyelement2, anyelement3, ...) returns text In order for this to work in general, we'd need FUNC_MAX_ARGS different types, which is currently defined as 100 in our code. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Another possible example is sprintf:
> create function sprintf(text, anyelement, anyelement2, anyelement3, ...)
> returns text
> In order for this to work in general, we'd need FUNC_MAX_ARGS different
> types, which is currently defined as 100 in our code.
But here, "any" would work perfectly fine, since there's no need for
any two arguments to be tied to each other or the result.
Given that we've got away so far with only 1 instance of anyelement,
I'm not really convinced that there's a market for more than anyelement2
(and anyarray2, etc).
regards, tom lane
On Sep 9, 2009, at 10:15 AM, Tom Lane wrote: >> In order for this to work in general, we'd need FUNC_MAX_ARGS >> different >> types, which is currently defined as 100 in our code. > > But here, "any" would work perfectly fine, since there's no need for > any two arguments to be tied to each other or the result. Well, only if you write your functions in C. I'd like to be able to write sprintf() in PL/pgSQL. Or PL/Perl, for that matter. Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 9, 2009, at 10:04 AM, Tom Lane wrote:
>> Well, yeah: it looks like a fertile source of security holes, not to
>> mention implementation difficulties (plpgsql really wants well-typed
>> expressions...). What you can do at the C level is not necessarily
>> sane to give to PL authors. I'm willing to consider a carefully
>> spec'd out proposal in this area, but "open the floodgates" ain't it.
> Security holes? Huh? What security holes would there be that you don't
> already have with anyelement?
Well, none, *if* it's defined to have exactly the same runtime behavior
as anyelement does. It sounded like you were arguing for something
looser. We could certainly define it as being just like anyelement
but not constrained to match any other argument or result (and, hence,
not usable as a result type).
regards, tom lane
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 9, 2009, at 10:15 AM, Tom Lane wrote:
>> But here, "any" would work perfectly fine, since there's no need for
>> any two arguments to be tied to each other or the result.
> Well, only if you write your functions in C. I'd like to be able to
> write sprintf() in PL/pgSQL. Or PL/Perl, for that matter.
I think you're confusing the point with a secondary issue, which is what
access we provide to these pseudotypes in PLs. To write sprintf in a
PL, you'd at least need the ability to cast "any" to text. I guess you
can do that with anyelement, though, so maybe there is nothing much here
except an overly restrictive safety check.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Another possible example is sprintf: > > > create function sprintf(text, anyelement, anyelement2, anyelement3, ...) > > returns text > > > In order for this to work in general, we'd need FUNC_MAX_ARGS different > > types, which is currently defined as 100 in our code. > > But here, "any" would work perfectly fine, since there's no need for > any two arguments to be tied to each other or the result. Yup. BTW does "any" match other pseudotypes? Would I be able to pass a cstring into "any"? That would create a large security hole I think. > Given that we've got away so far with only 1 instance of anyelement, > I'm not really convinced that there's a market for more than anyelement2 > (and anyarray2, etc). Well, if we have something general like a constrained "any", then I agree. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sep 9, 2009, at 10:17 AM, Tom Lane wrote: > Well, none, *if* it's defined to have exactly the same runtime > behavior > as anyelement does. It sounded like you were arguing for something > looser. We could certainly define it as being just like anyelement > but not constrained to match any other argument or result (and, hence, > not usable as a result type). Yes, that sounds about right. Is that not basically what Alvaro was looking for to start with? And is there an "any" array that could work for variadic functions like sprintf(), as well? Best, David
On Sep 9, 2009, at 10:22 AM, Tom Lane wrote: >> Well, only if you write your functions in C. I'd like to be able to >> write sprintf() in PL/pgSQL. Or PL/Perl, for that matter. > > I think you're confusing the point with a secondary issue, which is > what > access we provide to these pseudotypes in PLs. To write sprintf in a > PL, you'd at least need the ability to cast "any" to text. I guess > you > can do that with anyelement, though, so maybe there is nothing much > here > except an overly restrictive safety check. Yes, exactly. Best, David
Alvaro Herrera <alvherre@commandprompt.com> writes:
> BTW does "any" match other pseudotypes? Would I be able to pass a
> cstring into "any"? That would create a large security hole I think.
How so? 'Cause you can do that now with anyelement.
cstring is only a pseudotype for historical reasons, anyway --- there's
nothing about it now that's not a real type. I think we just have it
that way to discourage people from storing it in tables.
regards, tom lane
"David E. Wheeler" <david@kineticode.com> writes:
> Yes, that sounds about right. Is that not basically what Alvaro was
> looking for to start with? And is there an "any" array that could work
> for variadic functions like sprintf(), as well?
Well, no, because arrays are inherently all the same element type.
You could try to do sprintf as
sprintf(text, variadic anyarray) returns text
but this constrains all the arguments to be the same type, which is
not what you want. The variadic mechanism doesn't have the ability
to deal with what you're suggesting, and I'm not sure we want to try
to make it do that.
regards, tom lane
2009/9/9 David E. Wheeler <david@kineticode.com>: > On Sep 9, 2009, at 10:17 AM, Tom Lane wrote: > >> Well, none, *if* it's defined to have exactly the same runtime behavior >> as anyelement does. It sounded like you were arguing for something >> looser. We could certainly define it as being just like anyelement >> but not constrained to match any other argument or result (and, hence, >> not usable as a result type). > > Yes, that sounds about right. Is that not basically what Alvaro was looking > for to start with? And is there an "any" array that could work for variadic > functions like sprintf(), as well? no - because PostgreSQL doesn't support multitype array. So VARIADIC "any" isn't transformed to array and arguments are accessable via FunctionCallInfo structure. I thing, so this functionality is out of plpgsql or sql language, but when we are able to transform FunctionCallInfo to some perl or python structures, this can be accessed from plperl or plpythonu. Regards Pavel Stehule > > Best, > > David > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
2009/9/9 Tom Lane <tgl@sss.pgh.pa.us>: > "David E. Wheeler" <david@kineticode.com> writes: >> Yes, that sounds about right. Is that not basically what Alvaro was >> looking for to start with? And is there an "any" array that could work >> for variadic functions like sprintf(), as well? > > Well, no, because arrays are inherently all the same element type. > You could try to do sprintf as > > sprintf(text, variadic anyarray) returns text > > but this constrains all the arguments to be the same type, which is > not what you want. The variadic mechanism doesn't have the ability > to deal with what you're suggesting, and I'm not sure we want to try > to make it do that. variadic "any" isn't transformed to array. we are able to write sprintf(text, variadic "any") returns text, but only in C regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Pavel Stehule escribió: > we are able to write sprintf(text, variadic "any") returns text, but only in C Hmm, should we provide that function in core? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > Pavel Stehule escribió: > >> we are able to write sprintf(text, variadic "any") returns text, but only in C > > Hmm, should we provide that function in core? We should it, but I prefer some pgfoundry or contrib package. sprintf is really far to SQL. What more, we knows types, so some format tags are useless. Using original sprintf function is possible, but needs lot of code, because you need to transform PostgreSQL types to C types, and we have not any helping function for this task. Some similar to plpgsql's RAISE statement is some +/- 20 rows regards Pavel Stehule > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
On Sep 9, 2009, at 4:44 AM, Peter Eisentraut wrote: > That's beginning to sound a bit like a generics feature. E.g., > > CREATE FUNCTION the_same<T>(arg_a T, arg_b T) RETURNS bool AS $$ > SELECT arg_a IS DISTINCT FROM arg_b; > $$; mmm, yeah... ISTM that expansion in this area should probably head toward generics.. Does SQL spec such a thing?
Pavel Stehule escribió: > 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > > Pavel Stehule escribió: > > > >> we are able to write sprintf(text, variadic "any") returns text, but only in C > > > > Hmm, should we provide that function in core? > > We should it, but I prefer some pgfoundry or contrib package. sprintf > is really far to SQL. What more, we knows types, so some format tags > are useless. Using original sprintf function is possible, but needs > lot of code, because you need to transform PostgreSQL types to C > types, and we have not any helping function for this task. > > Some similar to plpgsql's RAISE statement is some +/- 20 rows I already published a pseudo-sprintf function in the wiki here: http://wiki.postgresql.org/wiki/Sprintf I'm looking for something better, not just the same hacks. I don't see any good reason that the function needs to be far from core. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sep 8, 2009, at 10:48 AM, David Fetter wrote: > I'd like to see pseudo-types like ANYNUMERIC, and allow it to take an > array decorator, which would really help for math-ish functions. Not > sure where that fits in this discussion. Perhaps typcategory could be leveraged here? ..Tho, if I understand the general direction, I think it would be along the lines of type classes/interfaces..
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > BTW does "any" match other pseudotypes? Would I be able to pass a
> > cstring into "any"? That would create a large security hole I think.
>
> How so? 'Cause you can do that now with anyelement.
Hmm, it doesn't seem to be allowed?
alvherre=# create function anyelem2 (anyelement) returns int language plpgsql as $$ begin return 1; end $$;
CREATE FUNCTION
alvherre=# select anyelem2(textout('oh'));
ERROR: PL/pgSQL functions cannot accept type cstring
CONTEXTO: compilation of PL/pgSQL function "anyelem2" near line 0
(BTW I find it a bit funny that lines are counted from 0. I never
noticed that before).
> cstring is only a pseudotype for historical reasons, anyway --- there's
> nothing about it now that's not a real type. I think we just have it
> that way to discourage people from storing it in tables.
Wow, it has I/O functions and all. Amazing, I wasn't aware of that.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Wed, Sep 9, 2009 at 1:15 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Another possible example is sprintf: > >> create function sprintf(text, anyelement, anyelement2, anyelement3, ...) >> returns text > >> In order for this to work in general, we'd need FUNC_MAX_ARGS different >> types, which is currently defined as 100 in our code. > > But here, "any" would work perfectly fine, since there's no need for > any two arguments to be tied to each other or the result. > > Given that we've got away so far with only 1 instance of anyelement, > I'm not really convinced that there's a market for more than anyelement2 > (and anyarray2, etc). I'm going to go out on a limb and say that if we're going to bother changing the status quo, we ought to create a reasonable number of these - maybe, say, four. I can't see needing a hundred of these, but I don't think that we should assume that our inability to think of a use for more than two at the moment implies that there can never be one. Really, I think we need a type system that doesn't try to represent every type as a 32-bit integer. Right now, for example, there's no reasonable way to write a function that takes another function as an argument. What we need is a system where base types are represented by an OID, but derived types (list and functional types) are built up using type constructors that take other types as arguments. So you could have a types like list(integer) or list(anyelement) or function(integer,bool) [meaning either taking an integer and returning a bool, or the other way around, depending on your notational preference]. Then you can have functions with complex types like: maplist : function(anyelement,anyelement2,function(list(anyelement),list(anyelement2))) This would have the fringe benefit of eliminating types like anyarray (which is just list(anyelement)) and the need to list every type twice in pg_type, once for the base type and once for the derived array type. </handwaving> ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> What we need is a system where base types are represented
> by an OID, but derived types (list and functional types) are built up
> using type constructors that take other types as arguments.
This is SQL, not Haskell. What you suggest seems about two orders of
magnitude more complex than real-world applications could justify.
(so where is pl/haskell, anyway?)
regards, tom lane
Robert Haas escribió: > Really, I think we need a type system that doesn't try to represent > every type as a 32-bit integer. Right now, for example, there's no > reasonable way to write a function that takes another function as an > argument. Function references would be neat -- I remember wanting to use these a couple of times (map/reduce?) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > Pavel Stehule escribió: >> 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: >> > Pavel Stehule escribió: >> > >> >> we are able to write sprintf(text, variadic "any") returns text, but only in C >> > >> > Hmm, should we provide that function in core? >> >> We should it, but I prefer some pgfoundry or contrib package. sprintf >> is really far to SQL. What more, we knows types, so some format tags >> are useless. Using original sprintf function is possible, but needs >> lot of code, because you need to transform PostgreSQL types to C >> types, and we have not any helping function for this task. >> >> Some similar to plpgsql's RAISE statement is some +/- 20 rows > > I already published a pseudo-sprintf function in the wiki here: > http://wiki.postgresql.org/wiki/Sprintf I'm looking for something > better, not just the same hacks. > > I don't see any good reason that the function needs to be far from core. what is use case? Why you need sprintf function, when you have || operator. This functionality is redundant and out of standard. What I know, only MySQL has similar function. Please, try to compile and run sprintf function from attachment postgres=# select sprintf('1:% 2:% 3:%', 10,null, 'kuku'); sprintf -------------------- 1:10 2:NULL 3:kuku (1 row) postgres=# select sprintf('Today is %, I am %.', current_date, current_user); sprintf ---------------------------------- Today is 2009-09-09, I am pavel. (1 row) Regards Pavel > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
Attachment
2009/9/9 Robert Haas <robertmhaas@gmail.com>: > On Wed, Sep 9, 2009 at 1:15 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >> Alvaro Herrera <alvherre@commandprompt.com> writes: >>> Another possible example is sprintf: >> >>> create function sprintf(text, anyelement, anyelement2, anyelement3, ...) >>> returns text >> >>> In order for this to work in general, we'd need FUNC_MAX_ARGS different >>> types, which is currently defined as 100 in our code. >> >> But here, "any" would work perfectly fine, since there's no need for >> any two arguments to be tied to each other or the result. >> >> Given that we've got away so far with only 1 instance of anyelement, >> I'm not really convinced that there's a market for more than anyelement2 >> (and anyarray2, etc). > > I'm going to go out on a limb and say that if we're going to bother > changing the status quo, we ought to create a reasonable number of > these - maybe, say, four. I can't see needing a hundred of these, but > I don't think that we should assume that our inability to think of a > use for more than two at the moment implies that there can never be > one. > > Really, I think we need a type system that doesn't try to represent > every type as a 32-bit integer. Right now, for example, there's no > reasonable way to write a function that takes another function as an > argument. What we need is a system where base types are represented > by an OID, but derived types (list and functional types) are built up > using type constructors that take other types as arguments. So you > could have a types like list(integer) or list(anyelement) or > function(integer,bool) [meaning either taking an integer and returning > a bool, or the other way around, depending on your notational > preference]. Then you can have functions with complex types like: > > maplist : function(anyelement,anyelement2,function(list(anyelement),list(anyelement2))) > > This would have the fringe benefit of eliminating types like anyarray > (which is just list(anyelement)) and the need to list every type twice > in pg_type, once for the base type and once for the derived array > type. it would be nice, but probably it could significant increase parsing query time. And this is +/- equal to what my transformationHook does. regards Pavel Stehule > > </handwaving> > > ...Robert > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Pavel Stehule escribió: > 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > > I already published a pseudo-sprintf function in the wiki here: > > http://wiki.postgresql.org/wiki/Sprintf I'm looking for something > > better, not just the same hacks. > > > > I don't see any good reason that the function needs to be far from core. > > what is use case? Why you need sprintf function, when you have || > operator. This functionality is redundant and out of standard. What I > know, only MySQL has similar function. Extensive use of || turns into horrible messes quickly. sprintf() makes this kind of thing much cleaner. You could use strcat/strcpy in C too, but do you? You could argue that sprintf is redundant in C, yet it turns out to be extremely useful. One use case is using it for error messages in RAISE/USING. Yes, I am aware you can use concatenation there. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > Pavel Stehule escribió: >> 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > >> > I already published a pseudo-sprintf function in the wiki here: >> > http://wiki.postgresql.org/wiki/Sprintf I'm looking for something >> > better, not just the same hacks. >> > >> > I don't see any good reason that the function needs to be far from core. >> >> what is use case? Why you need sprintf function, when you have || >> operator. This functionality is redundant and out of standard. What I >> know, only MySQL has similar function. > > Extensive use of || turns into horrible messes quickly. sprintf() makes > this kind of thing much cleaner. You could use strcat/strcpy in C too, > but do you? You could argue that sprintf is redundant in C, yet it > turns out to be extremely useful. > Yes, I agree. But this functionality you will use only in plpgsql language. I thing, so there could be some library that should be separated from standard functions. It would be nice, when people clearly understand if use some enhancing functionality or some base sql functionality. we could to have schema plpgsql. And there could be function subst, then in your plpgsql proc you can call if (...) then message := plpgsql.subst('some message:% ...', some value, ...); .... if you would, then you can add plpgsql schema to search path. I dislike to use name sprintf, because this or similar function isn't real sprintf function - it doesn't use compatible format string with sprintf function. regards Pavel Stehule > One use case is using it for error messages in RAISE/USING. Yes, I am > aware you can use concatenation there. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
On Wed, 2009-09-09 at 09:39 -0400, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Well, so far we've only seen use cases in this thread that either > > already work or that are not well-defined. ;-) > > Well, yeah, the question is can we extract a clear TODO item here. > > I think there are two somewhat orthogonal issues: > > 1. Is a completely unconstrained argument type (ie "any") of any real > use to PL functions, and if so how can we expose that usefulness? > The only clear thing to do with such an argument is IS NULL/IS NOT NULL > tests, which might or might not be worth the trouble. > > 2. Is there any use for arguments with type constraints not covered > by the existing ANYFOO rules, and if so what do we add for that? > > One comment on point 2 is that it was foreseen from the beginning > that there would be need for ANYELEMENT2 etc, and I'm actually rather > surprised that we've gone this long without adding them. Where we could need anyelement2 and enyelement3 is if we need the sameness of any 2 parameters or OUT parameter types maybe we could (re/ab)use parametrized types and define anyelement(1), anyelement(2), ..., anyelement(N) and then match them by the number in parentheses > Alvaro made > a good point about not wanting to multiply the various hard-wired > OID references, but perhaps some judicious code refactoring could > prevent a notational disaster. > > regards, tom lane -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
On Wed, 2009-09-09 at 15:10 -0400, Robert Haas wrote: > On Wed, Sep 9, 2009 at 1:15 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > > Alvaro Herrera <alvherre@commandprompt.com> writes: > >> Another possible example is sprintf: > > > >> create function sprintf(text, anyelement, anyelement2, anyelement3, ...) > >> returns text > > > >> In order for this to work in general, we'd need FUNC_MAX_ARGS different > >> types, which is currently defined as 100 in our code. > > > > But here, "any" would work perfectly fine, since there's no need for > > any two arguments to be tied to each other or the result. > > > > Given that we've got away so far with only 1 instance of anyelement, > > I'm not really convinced that there's a market for more than anyelement2 > > (and anyarray2, etc). > > I'm going to go out on a limb and say that if we're going to bother > changing the status quo, we ought to create a reasonable number of > these - maybe, say, four. I can't see needing a hundred of these, but > I don't think that we should assume that our inability to think of a > use for more than two at the moment implies that there can never be > one. > > Really, I think we need a type system that doesn't try to represent > every type as a 32-bit integer. Right now, for example, there's no > reasonable way to write a function that takes another function as an > argument. What we need is a system where base types are represented > by an OID, but derived types (list and functional types) are built up > using type constructors that take other types as arguments. There is nothing that prevents us from representing those by an OID as well. Though how to define and store those in pg_type is another issue. > So you > could have a types like list(integer) or list(anyelement) or > function(integer,bool) [meaning either taking an integer and returning > a bool, or the other way around, depending on your notational > preference]. Then you can have functions with complex types like: > > maplist : function(anyelement,anyelement2,function(list(anyelement),list(anyelement2))) > > This would have the fringe benefit of eliminating types like anyarray > (which is just list(anyelement)) and the need to list every type twice > in pg_type, once for the base type and once for the derived array > type. > > </handwaving> > > ...Robert -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
Hannu Krosing <hannu@2ndQuadrant.com> writes:
> maybe we could (re/ab)use parametrized types and define
> anyelement(1), anyelement(2), ..., anyelement(N) and then match them by
> the number in parentheses
Yeah, that idea occurred to me too. The immediate practical problem is
that we don't store a typmod for function argument/result types.
I guess we could look into doing that ...
regards, tom lane
2009/9/9 Hannu Krosing <hannu@krosing.net>: > On Wed, 2009-09-09 at 21:57 +0200, Pavel Stehule wrote: >> 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: >> > Pavel Stehule escribió: >> >> 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: >> > >> >> > I already published a pseudo-sprintf function in the wiki here: >> >> > http://wiki.postgresql.org/wiki/Sprintf I'm looking for something >> >> > better, not just the same hacks. >> >> > >> >> > I don't see any good reason that the function needs to be far from core. >> >> >> >> what is use case? Why you need sprintf function, when you have || >> >> operator. This functionality is redundant and out of standard. What I >> >> know, only MySQL has similar function. >> > >> > Extensive use of || turns into horrible messes quickly. sprintf() makes >> > this kind of thing much cleaner. You could use strcat/strcpy in C too, >> > but do you? You could argue that sprintf is redundant in C, yet it >> > turns out to be extremely useful. >> > >> >> Yes, I agree. But this functionality you will use only in plpgsql >> language. I thing, so there could be some library that should be >> separated from standard functions. It would be nice, when people >> clearly understand if use some enhancing functionality or some base >> sql functionality. > > There is lots of stuff in postgreSQL, especially functions, that is not > "some base sql functionality", yet is in core. > >> >> we could to have schema plpgsql. And there could be function subst, >> >> then in your plpgsql proc you can call >> >> if (...) then >> message := plpgsql.subst('some message:% ...', some value, ...); >> .... >> >> if you would, then you can add plpgsql schema to search path. >> >> I dislike to use name sprintf, because this or similar function isn't >> real sprintf function - it doesn't use compatible format string with >> sprintf function. > > call it format(txt, variadic "any") - that's what it does why not? this function should be in contrib - as variadic function sample. Pavel > >> regards >> Pavel Stehule >> >> > One use case is using it for error messages in RAISE/USING. Yes, I am >> > aware you can use concatenation there. >> > >> > -- >> > Alvaro Herrera http://www.CommandPrompt.com/ >> > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >> > >> > >
On Wed, 2009-09-09 at 21:57 +0200, Pavel Stehule wrote: > 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > > Pavel Stehule escribió: > >> 2009/9/9 Alvaro Herrera <alvherre@commandprompt.com>: > > > >> > I already published a pseudo-sprintf function in the wiki here: > >> > http://wiki.postgresql.org/wiki/Sprintf I'm looking for something > >> > better, not just the same hacks. > >> > > >> > I don't see any good reason that the function needs to be far from core. > >> > >> what is use case? Why you need sprintf function, when you have || > >> operator. This functionality is redundant and out of standard. What I > >> know, only MySQL has similar function. > > > > Extensive use of || turns into horrible messes quickly. sprintf() makes > > this kind of thing much cleaner. You could use strcat/strcpy in C too, > > but do you? You could argue that sprintf is redundant in C, yet it > > turns out to be extremely useful. > > > > Yes, I agree. But this functionality you will use only in plpgsql > language. I thing, so there could be some library that should be > separated from standard functions. It would be nice, when people > clearly understand if use some enhancing functionality or some base > sql functionality. There is lots of stuff in postgreSQL, especially functions, that is not "some base sql functionality", yet is in core. > > we could to have schema plpgsql. And there could be function subst, > > then in your plpgsql proc you can call > > if (...) then > message := plpgsql.subst('some message:% ...', some value, ...); > .... > > if you would, then you can add plpgsql schema to search path. > > I dislike to use name sprintf, because this or similar function isn't > real sprintf function - it doesn't use compatible format string with > sprintf function. call it format(txt, variadic "any") - that's what it does > regards > Pavel Stehule > > > One use case is using it for error messages in RAISE/USING. Yes, I am > > aware you can use concatenation there. > > > > -- > > Alvaro Herrera http://www.CommandPrompt.com/ > > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > >
On Wed, Sep 09, 2009 at 03:23:52PM -0400, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > What we need is a system where base types are represented > > by an OID, but derived types (list and functional types) are built up > > using type constructors that take other types as arguments. > > This is SQL, not Haskell. What you suggest seems about two orders of > magnitude more complex than real-world applications could justify. Parametric polymorphism seems to have made it well into the mainstream now, Java, C# and many other "mainstream" languages have got it, it's not just ML and Haskell any more. Excuses of it being of esoteric academic interest alone seem a little misplaced. It would also tidy up a lot of the features that already exist in PG. Arrays in PG already effectively have a type parameter, why not extend this to normal user's code? Even staying within the types in PG, I've wanted to use the geometric functions parametrized over integer and numeric types before, fixing them to double precision types seems unfortunate. -- Sam http://samason.me.uk/
On Sep 9, 2009, at 2:36 PM, Alvaro Herrera wrote: > Robert Haas escribió: > >> Really, I think we need a type system that doesn't try to represent >> every type as a 32-bit integer. Right now, for example, there's no >> reasonable way to write a function that takes another function as an >> argument. > > Function references would be neat -- I remember wanting to use these a > couple of times (map/reduce?) Yeah, I recall having a want for that as well, though I can't remember what the use case was now. :/ Though that kind of flexibility is probably the most complete solution, going with the idea of anyelement(N) might be a lot more practical... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Hi, Tom Lane <tgl@sss.pgh.pa.us> writes: > Hannu Krosing <hannu@2ndQuadrant.com> writes: >> anyelement(1), anyelement(2), ..., anyelement(N) and then match them by >> the number in parentheses > > Yeah, that idea occurred to me too. The immediate practical problem is > that we don't store a typmod for function argument/result types. > I guess we could look into doing that ... But still, it looks terrible... On Sep 9, 2009, at 4:44 AM, Peter Eisentraut wrote: > That's beginning to sound a bit like a generics feature. E.g., > > CREATE FUNCTION the_same<T>(arg_a T, arg_b T) RETURNS bool AS $$ > SELECT arg_a IS DISTINCT FROM arg_b; > $$; And even if we don't want to go this far (I'd be in favor of going there FWIW), we could maybe have a syntax allowing the users to name or declare the any types he'll need? CREATE FUNCTION foo(a anyelement x, b anyelement x, c anyelement y)RETURNS anyelement y[] AS $$ ... $$; Now we have anyelement and anyelement2, but without the ugly names or the typmod feature stretching, and we can even have any number of user defined anyelement types. That behave just like anyelement. Then, maybe we need VARIADIC anyelement any[] to declare the function as able to cope with a variable length list of all different kinds of elements? Regards, -- dim
On Wed, Sep 9, 2009 at 3:23 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> What we need is a system where base types are represented >> by an OID, but derived types (list and functional types) are built up >> using type constructors that take other types as arguments. > > This is SQL, not Haskell. What you suggest seems about two orders of > magnitude more complex than real-world applications could justify. > > (so where is pl/haskell, anyway?) There are languages much less obscure than Haskell that support passing functions as arguments to other functions, such as C. While C doesn't support user-defined type constructors, it does support one built-in type constructor - you can declare a function argument as taking arbitrary argument types and returning an arbitrary type. C++ supports user-defined type constructors via the template mechanism. The scripting languages generally do not guarantee type-safety for functions passed as arguments, but they do let you pass them. However, I can't really imagine how we could get away with such a system in SQL, due to security concerns. ...Robert
On Thu, 2009-09-10 at 00:31 +0200, Dimitri Fontaine wrote: > Hi, > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > Hannu Krosing <hannu@2ndQuadrant.com> writes: > >> anyelement(1), anyelement(2), ..., anyelement(N) and then match them by > >> the number in parentheses > > > > Yeah, that idea occurred to me too. The immediate practical problem is > > that we don't store a typmod for function argument/result types. > > I guess we could look into doing that ... > > But still, it looks terrible... > > On Sep 9, 2009, at 4:44 AM, Peter Eisentraut wrote: > > That's beginning to sound a bit like a generics feature. E.g., > > > > CREATE FUNCTION the_same<T>(arg_a T, arg_b T) RETURNS bool AS $$ > > SELECT arg_a IS DISTINCT FROM arg_b; > > $$; > > And even if we don't want to go this far (I'd be in favor of going there > FWIW), we could maybe have a syntax allowing the users to name or > declare the any types he'll need? > > CREATE FUNCTION foo(a anyelement x, b anyelement x, c anyelement y) > RETURNS anyelement y[] > AS $$ > ... > $$; > > Now we have anyelement and anyelement2, but without the ugly names or > the typmod feature stretching, and we can even have any number of user > defined anyelement types. That behave just like anyelement. > > Then, maybe we need VARIADIC anyelement any[] to declare the function as able > to cope with a variable length list of all different kinds of elements? maybe just let users say what they mean, so first time we have "any" and if we need more then we say "same_as(...)" so your example becomes CREATE FUNCTION foo(a any, b same_type_as(a), c any) RETURNS same_type_as(c)[]AS $$... $$; or the same using positional arguments CREATE FUNCTION foo(a any, b same_type_as(1), c any) RETURNS same_type_as(3)[]AS $$... $$; this then gets transformed at parse time to whatever internal representation of type sameness we use. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
On Thu, 2009-09-10 at 08:44 +0300, Hannu Krosing wrote: > maybe just let users say what they mean, so first time we have "any" and > if we need more then we say "same_as(...)" Acutually we could be even more SQL-y and have a more verbose syntax for pseudotypes by extending the grammar CREATE FUNCTION foo( a ANY TYPE, b SAME TYPE AS a, c ANY TYPE OUT d ARRAY OF SAME TYPE AS c ) .... TYPE could probably be optional > so your example becomes > > > CREATE FUNCTION foo(a any, b same_type_as(a), c any) > RETURNS same_type_as(c)[] > AS $$ > ... > $$; > > or the same using positional arguments > > CREATE FUNCTION foo(a any, b same_type_as(1), c any) > RETURNS same_type_as(3)[] > AS $$ > ... > $$; > > this then gets transformed at parse time to whatever internal > representation of type sameness we use. > > -- > Hannu Krosing http://www.2ndQuadrant.com > PostgreSQL Scalability and Availability > Services, Consulting and Training > > >
On Wed, 2009-09-09 at 16:31 -0400, Tom Lane wrote: > The immediate practical problem is > that we don't store a typmod for function argument/result types. > I guess we could look into doing that ... I think that functionality could also end up being useful for other types.
2009/9/10 Hannu Krosing <hannu@krosing.net>: > On Thu, 2009-09-10 at 08:44 +0300, Hannu Krosing wrote: > >> maybe just let users say what they mean, so first time we have "any" and >> if we need more then we say "same_as(...)" > > Acutually we could be even more SQL-y and have a more verbose syntax for > pseudotypes by extending the grammar > > CREATE FUNCTION foo( > a ANY TYPE, > b SAME TYPE AS a, > c ANY TYPE > OUT d ARRAY OF SAME TYPE AS c > ) .... > > TYPE could probably be optional > We could define anything, but we have to implement function searching algorithm, that understand this new feature, and that is compatible with current behave. Syntax is some simple, but the core is in namespace.c. regards Pavel > >> so your example becomes >> >> >> CREATE FUNCTION foo(a any, b same_type_as(a), c any) >> RETURNS same_type_as(c)[] >> AS $$ >> ... >> $$; >> >> or the same using positional arguments >> >> CREATE FUNCTION foo(a any, b same_type_as(1), c any) >> RETURNS same_type_as(3)[] >> AS $$ >> ... >> $$; >> >> this then gets transformed at parse time to whatever internal >> representation of type sameness we use. >> >> -- >> Hannu Krosing http://www.2ndQuadrant.com >> PostgreSQL Scalability and Availability >> Services, Consulting and Training >> >> >> > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Robert Haas <robertmhaas@gmail.com> wrote: > There are languages much less obscure than Haskell that support > passing functions as arguments to other functions, such as C. Or Java, which lets you, for example, pass a Class or Method as an argument, and includes support for generics. I see that pgfoundry has pl/Java, which has an activity percentile of 91.08%. I'm not sure whether this could address the needs that started this discussion, since I assume such capabilities would only be usable when invoking one Java method from another. I'm just saying -- these features aren't all that esoteric; we use introspection and reflection within our Java software. While we probably wouldn't use such features in PostgreSQL (if they were there) because of our portability mandate; I can certainly understand those who don't mind PostgreSQL-specific code and want to move more of the business logic to the DBMS wanting such features. -Kevin
Pavel Stehule escribió:
> Please, try to compile and run sprintf function from attachment
There's a minor bug in the comparison to PG_NARGS() inside the loop,
fixed in this version.
The one problem I have with this is that if the format string does not
contain any % (and thus there is no extra argument), it errors out:
alvherre=# select text_format('ouch');
ERROR: function text_format(unknown) does not exist
LÍNEA 1: select text_format('ouch');
^
SUGERENCIA: No function matches the given name and argument types. You might need to add explicit type casts.
AFAICS fixing this would require a second pg_proc entry for this
function.
alvherre=# select text_format('% was % at % and said % % times', 'Pavel'::text, 'here'::unknown, now(),
row('a','b','c'),'{42}'::int[]);
text_format
-----------------------------------------------------------------------------
Pavel was here at 2009-09-10 13:12:09.054653-04 and said (a,b,c) {42} times
(1 fila)
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachment
Alvaro Herrera <alvherre@commandprompt.com> writes:
> alvherre=# select text_format('% was % at % and said % % times', 'Pavel'::text, 'here'::unknown, now(),
row('a','b','c'),'{42}'::int[]);
> text_format
> -----------------------------------------------------------------------------
> Pavel was here at 2009-09-10 13:12:09.054653-04 and said (a,b,c) {42} times
> (1 fila)
Is that what's being proposed? That pretty much sucks --- it's just
another way of concatenating some strings. I thought the idea was to
provide the same power as sprintf, eg field width controls, numeric
formatting options, etc.
regards, tom lane
On Sep 10, 2009, at 10:16 AM, Tom Lane wrote: > Is that what's being proposed? I think that's what currently works, given the limitations of arrays (variadic arguments) to a single data type. > That pretty much sucks --- it's just > another way of concatenating some strings. I thought the idea was to > provide the same power as sprintf, eg field width controls, numeric > formatting options, etc. That would be the goal, yes. Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Sep 10, 2009, at 10:16 AM, Tom Lane wrote:
>> Is that what's being proposed?
> I think that's what currently works, given the limitations of arrays
> (variadic arguments) to a single data type.
Well, at the very least the parameter markers should be spelled "%s",
so that there's some hope of upward compatibility with a more complete
implementation.
regards, tom lane
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> alvherre=# select text_format('% was % at % and said % % times', 'Pavel'::text, 'here'::unknown, now(),
row('a','b','c'),'{42}'::int[]);
>> text_format
>> -----------------------------------------------------------------------------
>> Pavel was here at 2009-09-10 13:12:09.054653-04 and said (a,b,c) {42} times
>> (1 fila)
>
> Is that what's being proposed? That pretty much sucks --- it's just
> another way of concatenating some strings. I thought the idea was to
> provide the same power as sprintf, eg field width controls, numeric
> formatting options, etc.
>
I thing so this is enough - we can get simply message text - like
raise notice statement. I thing so simple and clean function has more
usability than heavy real sprintf function. We (c coders) are old
dinosaurs - but nobody else knows what sprintf function does.
I thing so the name only "format" is good, it's short. If you need
some other formating, just you can use to_char function.
Pavel
> regards, tom lane
>
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: > "David E. Wheeler" <david@kineticode.com> writes: >> On Sep 10, 2009, at 10:16 AM, Tom Lane wrote: >>> Is that what's being proposed? > >> I think that's what currently works, given the limitations of arrays >> (variadic arguments) to a single data type. no, in my code is nothing transformed to single data type. > > Well, at the very least the parameter markers should be spelled "%s", > so that there's some hope of upward compatibility with a more complete > implementation. I thing so people who knows sprintf function could be confused. It's ok for any text types, but for datetime, numeric types and others? More natural is using complete sprintf's tags, but it is far to friendly using. So it is reason why I am against to sprintf implementation in postgres, and I am for implementation some different function, that just simplify formatting. format function should be nice with new raise statement syntax. regards Pavel Stehule > > regards, tom lane >
2009/9/10 David E. Wheeler <david@kineticode.com>: > On Sep 10, 2009, at 10:16 AM, Tom Lane wrote: > >> Is that what's being proposed? > > I think that's what currently works, given the limitations of arrays > (variadic arguments) to a single data type. > >> That pretty much sucks --- it's just >> another way of concatenating some strings. I thought the idea was to >> provide the same power as sprintf, eg field width controls, numeric >> formatting options, etc. > > That would be the goal, yes. no - we have to_char function, why we need different formatting system? Pavel > > Best, > > David >
Pavel Stehule <pavel.stehule@gmail.com> writes:
>> On Sep 10, 2009, at 10:16 AM, Tom Lane wrote:
>>> I thought the idea was to
>>> provide the same power as sprintf, eg field width controls, numeric
>>> formatting options, etc.
> no - we have to_char function, why we need different formatting system?
Why do we need this at all, when we have the concatenation operator?
I think the point of it is that people are used to how sprintf works.
So it should work as nearly like sprintf as possible.
regards, tom lane
On Sep 10, 2009, at 11:16 AM, Tom Lane wrote: >> no - we have to_char function, why we need different formatting >> system? > > Why do we need this at all, when we have the concatenation operator? > I think the point of it is that people are used to how sprintf works. > So it should work as nearly like sprintf as possible. +1 David
On Wed, Sep 9, 2009 at 3:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Really, I think we need a type system that doesn't try to represent >> every type as a 32-bit integer. Right now, for example, there's no >> reasonable way to write a function that takes another function as an >> argument. What we need is a system where base types are represented >> by an OID, but derived types (list and functional types) are built up >> using type constructors that take other types as arguments. So you >> could have a types like list(integer) or list(anyelement) or >> function(integer,bool) [meaning either taking an integer and returning >> a bool, or the other way around, depending on your notational >> preference]. Then you can have functions with complex types like: >> >> maplist : function(anyelement,anyelement2,function(list(anyelement),list(anyelement2))) >> >> This would have the fringe benefit of eliminating types like anyarray >> (which is just list(anyelement)) and the need to list every type twice >> in pg_type, once for the base type and once for the derived array >> type. > > it would be nice, but probably it could significant increase parsing > query time. And this is +/- equal to what my transformationHook does. I can't believe that this is even close to being correct. Transformationhook is a cheap syntax hack (sorry, but it is). It's not going to solve the problem of people who want anyelement and anyelement2, nor will it solve the problem of people who want to pass functions as arguments or treat them as first-class objects. The major downside of such a system is that every place where we now count on being able to store a type in a fixed-size field would need to be touched. I don't believe that the overall slowdown in parsing time would be significant, but I do think it would be a massive, highly invasive, highly destabilizing patch. For the level of pain involved, there might be better uses of our time, which is not to say that I'd be in favor of rejecting such a patch out of hand if someone felt called to develop it (Tom might, though). ...Robert
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>>> On Sep 10, 2009, at 10:16 AM, Tom Lane wrote:
>>>> I thought the idea was to
>>>> provide the same power as sprintf, eg field width controls, numeric
>>>> formatting options, etc.
>
>> no - we have to_char function, why we need different formatting system?
>
> Why do we need this at all, when we have the concatenation operator?
what is more readable?
select 'i=' || i || ', b=' || b || ', c=' || c ..
or
select format('i=%, b=%, c=%', i, b, c ..)
> I think the point of it is that people are used to how sprintf works.
> So it should work as nearly like sprintf as possible.
>
How sprintf will be print bytea type, or char(n) type values? I can
understand, so people like some what is well known, but sprintf
function is from other domain than databases. There isn't possible
100% compatible implementation - because sprintf desn't knows arrays,
custom types, rows.
> regards, tom lane
>
2009/9/10 Robert Haas <robertmhaas@gmail.com>: > On Wed, Sep 9, 2009 at 3:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> Really, I think we need a type system that doesn't try to represent >>> every type as a 32-bit integer. Right now, for example, there's no >>> reasonable way to write a function that takes another function as an >>> argument. What we need is a system where base types are represented >>> by an OID, but derived types (list and functional types) are built up >>> using type constructors that take other types as arguments. So you >>> could have a types like list(integer) or list(anyelement) or >>> function(integer,bool) [meaning either taking an integer and returning >>> a bool, or the other way around, depending on your notational >>> preference]. Then you can have functions with complex types like: >>> >>> maplist : function(anyelement,anyelement2,function(list(anyelement),list(anyelement2))) >>> >>> This would have the fringe benefit of eliminating types like anyarray >>> (which is just list(anyelement)) and the need to list every type twice >>> in pg_type, once for the base type and once for the derived array >>> type. >> >> it would be nice, but probably it could significant increase parsing >> query time. And this is +/- equal to what my transformationHook does. > > I can't believe that this is even close to being correct. > Transformationhook is a cheap syntax hack (sorry, but it is). It's > not going to solve the problem of people who want anyelement and > anyelement2, nor will it solve the problem of people who want to pass > functions as arguments or treat them as first-class objects. nobody written some better code. Just implement some too simple like Oracle's decode function, and then talk about this topic. I like to see this discus, but who looked on FuncnameGetCandidates functions, and who knows, what is really possible. regards Pavel > > The major downside of such a system is that every place where we now > count on being able to store a type in a fixed-size field would need > to be touched. I don't believe that the overall slowdown in parsing > time would be significant, but I do think it would be a massive, > highly invasive, highly destabilizing patch. For the level of pain > involved, there might be better uses of our time, which is not to say > that I'd be in favor of rejecting such a patch out of hand if someone > felt called to develop it (Tom might, though). > > ...Robert >
Robert Haas <robertmhaas@gmail.com> writes:
> The major downside of such a system is that every place where we now
> count on being able to store a type in a fixed-size field would need
> to be touched. I don't believe that the overall slowdown in parsing
> time would be significant, but I do think it would be a massive,
> highly invasive, highly destabilizing patch. For the level of pain
> involved, there might be better uses of our time,
Yeah, that's exactly the problem.
I am not sure that we really *have to* have a non-OID-based type
representation though. We have managed to have composite types without
that, and I don't see why something similar would not work for
functional types.
But that's all well beyond the immediate problem, which is whether we
need something more flexible than "anyelement". ISTM we had these
not-all-mutually-exclusive ideas on the table:
1. Allow the existing "any" pseudotype as an input argument type for PLs.
(AFAICS this is simple and painless; about the only question is whether
we want to keep using the name "any", which because of conflicting with
a reserved word would always need the double quotes.)
2. Come up with some way to do the equivalent of "variadic any[]",
ie, a variable number of not-all-the-same-type arguments. (This isn't
just a type-system problem, there's also the question of how the type
information would be passed at runtime. IIRC we have a solution at the
C level but not for PLs.)
3. Add anyelement2/anyarray2, and maybe also -3 and -4 while at it.
4. Instead of #3, allow anyelement(N), which is certainly more flexible
than #3 but would require a much larger investment of work. (I'm
uncertain whether attaching typmods to function arguments/results could
have any interesting or unpleasant semantic side effects. It might be
all good, or maybe not. It would definitely need some thought.)
5. Various syntactic sugar to substitute for anyelement. (Not in favor
of this myself, it seems to just complicate matters.)
Functional types might be interesting in the long run but I don't see
that they alter the need for one or more of these.
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> wrote:
> what is more readable?
>
> select 'i=' || i || ', b=' || b || ', c=' || c ..
>
> or
>
> select format('i=%, b=%, c=%', i, b, c ..)
Seriously, those are about dead even for me. The concatenation
might have a slight edge, particularly since I have the option, if
it gets out of hand, to do:
select 'i=' || i || ', b=' || b || ', c=' || c ..
-Kevin
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
>> I think the point of it is that people are used to how sprintf works.
>> So it should work as nearly like sprintf as possible.
> How sprintf will be print bytea type, or char(n) type values?
Well, that's why it requires some actual thought and agreement on a
specification --- sprintf just crashes on type mismatches, but perhaps
the SQL version should be smarter. You shouldn't expect that the
easiest thing to throw together is going to be considered the most
desirable solution.
regards, tom lane
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
>>> I think the point of it is that people are used to how sprintf works.
>>> So it should work as nearly like sprintf as possible.
>
>> How sprintf will be print bytea type, or char(n) type values?
>
> Well, that's why it requires some actual thought and agreement on a
> specification --- sprintf just crashes on type mismatches, but perhaps
> the SQL version should be smarter. You shouldn't expect that the
> easiest thing to throw together is going to be considered the most
> desirable solution.
I don't afraid about crashing. Simply I have not idea what sql
sprintf's behave in case:
SELECT sprintf('some %s', 10)
or
SELECT sprintf('some %d', 10::mycustomtype)
???
>
> regards, tom lane
>
On Thu, Sep 10, 2009 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> The major downside of such a system is that every place where we now >> count on being able to store a type in a fixed-size field would need >> to be touched. I don't believe that the overall slowdown in parsing >> time would be significant, but I do think it would be a massive, >> highly invasive, highly destabilizing patch. For the level of pain >> involved, there might be better uses of our time, > > Yeah, that's exactly the problem. > > I am not sure that we really *have to* have a non-OID-based type > representation though. We have managed to have composite types without > that, and I don't see why something similar would not work for > functional types. It might be possible to make it work, but it's likely to create a lot of bloat in pg_type, and will make it very difficult to implement features such as anonymous functions (i.e. LAMBDA). I think it's further embedding a not-particularly-great design decision. We've already patched around the insufficiency of representing types as 32-bit integers for types by adding typmods, but that solution is incompletely implemented (as exemplified by your comments below under #4, and I don't think that's the only example) and doesn't handle all the interesting cases. Maybe we should move in the direction of having some kind of semi-opaque Type object that for now can contain a typid and typmod, and always compare types by using some abstract function that operates on Types. > But that's all well beyond the immediate problem, which is whether we > need something more flexible than "anyelement". ISTM we had these > not-all-mutually-exclusive ideas on the table: > > 1. Allow the existing "any" pseudotype as an input argument type for PLs. > (AFAICS this is simple and painless; about the only question is whether > we want to keep using the name "any", which because of conflicting with > a reserved word would always need the double quotes.) +1 from me. I could go either way on changing the name, but if we're to do it, first we'll have to think of something better than "any", which might not be so easy. > 2. Come up with some way to do the equivalent of "variadic any[]", > ie, a variable number of not-all-the-same-type arguments. (This isn't > just a type-system problem, there's also the question of how the type > information would be passed at runtime. IIRC we have a solution at the > C level but not for PLs.) This also seems like a good idea. Will pg_typeof() work for PL/pgsql?I think if we can come up with a solution for PL/pgsql,it would be reasonable to commit this, leaving the tidying up for any PLs for which the right solution is non-obvious to someone who wants the feature enough to propose an appropriate patch. > 3. Add anyelement2/anyarray2, and maybe also -3 and -4 while at it. > > 4. Instead of #3, allow anyelement(N), which is certainly more flexible > than #3 but would require a much larger investment of work. (I'm > uncertain whether attaching typmods to function arguments/results could > have any interesting or unpleasant semantic side effects. It might be > all good, or maybe not. It would definitely need some thought.) Of these two, I prefer #4, but I don't think #3 would be horrible either. > 5. Various syntactic sugar to substitute for anyelement. (Not in favor > of this myself, it seems to just complicate matters.) I agree; I don't think this solves any real problem. ...Robert
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: >>> I think the point of it is that people are used to how sprintf works. >>> So it should work as nearly like sprintf as possible. > >> How sprintf will be print bytea type, or char(n) type values? > > Well, that's why it requires some actual thought and agreement on a > specification --- sprintf just crashes on type mismatches, but perhaps > the SQL version should be smarter. You shouldn't expect that the > easiest thing to throw together is going to be considered the most > desirable solution. I agree. It's time. regards Pavel > > regards, tom lane >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I don't afraid about crashing. Simply I have not idea what sql
> sprintf's behave in case:
> SELECT sprintf('some %s', 10)
That one I don't think is hard --- coerce the input type to text and
print the string.
> SELECT sprintf('some %d', 10::mycustomtype)
For the formats that presume an integer or float input in C, perhaps
we could coerce to numeric (failing if that fails) and then print
appropriately. Or maybe int or float8 would be more appropriate
conversion targets.
regards, tom lane
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Sep 10, 2009 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 2. Come up with some way to do the equivalent of "variadic any[]",
>> ie, a variable number of not-all-the-same-type arguments. �(This isn't
>> just a type-system problem, there's also the question of how the type
>> information would be passed at runtime. �IIRC we have a solution at the
>> C level but not for PLs.)
> This also seems like a good idea. Will pg_typeof() work for PL/pgsql?
pg_typeof() applied to what? The existing approach assumes we can make
an array out of the variadic parameters, which isn't going to be the
case here.
regards, tom lane
good analyse, thank you 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: > Robert Haas <robertmhaas@gmail.com> writes: >> The major downside of such a system is that every place where we now >> count on being able to store a type in a fixed-size field would need >> to be touched. I don't believe that the overall slowdown in parsing >> time would be significant, but I do think it would be a massive, >> highly invasive, highly destabilizing patch. For the level of pain >> involved, there might be better uses of our time, > > Yeah, that's exactly the problem. > > I am not sure that we really *have to* have a non-OID-based type > representation though. We have managed to have composite types without > that, and I don't see why something similar would not work for > functional types. > > But that's all well beyond the immediate problem, which is whether we > need something more flexible than "anyelement". ISTM we had these > not-all-mutually-exclusive ideas on the table: > > 1. Allow the existing "any" pseudotype as an input argument type for PLs. > (AFAICS this is simple and painless; about the only question is whether > we want to keep using the name "any", which because of conflicting with > a reserved word would always need the double quotes.) > I thing so this is possible - I see only one critical point - you cannot validate source in validation time. You have to wait for run time. This is some what we leave in plpgsql. I agree, so this is some time one possible way for high level programming. > 2. Come up with some way to do the equivalent of "variadic any[]", > ie, a variable number of not-all-the-same-type arguments. (This isn't > just a type-system problem, there's also the question of how the type > information would be passed at runtime. IIRC we have a solution at the > C level but not for PLs.) again - this is possible, but there could be some strange cases - because we have not polymorphic assign statement - and there are not real some polymorphic variable type like variant or some similar (but it not big problem to implement it). The bigger problem is cached plan in assign statement, or other statements - minimally in plpgsql. > > 3. Add anyelement2/anyarray2, and maybe also -3 and -4 while at it. > > 4. Instead of #3, allow anyelement(N), which is certainly more flexible > than #3 but would require a much larger investment of work. (I'm > uncertain whether attaching typmods to function arguments/results could > have any interesting or unpleasant semantic side effects. It might be > all good, or maybe not. It would definitely need some thought.) anyelement(n) looks more general. I am not sure about readability. > > 5. Various syntactic sugar to substitute for anyelement. (Not in favor > of this myself, it seems to just complicate matters.) > > Functional types might be interesting in the long run but I don't see > that they alter the need for one or more of these. > There could be some use cases like work-flow systems or maybe scheduler event executor. regards Pavel Stehule > regards, tom lane >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
>> 1. Allow the existing "any" pseudotype as an input argument type for PLs.
>> (AFAICS this is simple and painless; about the only question is whether
>> we want to keep using the name "any", which because of conflicting with
>> a reserved word would always need the double quotes.)
> I thing so this is possible - I see only one critical point - you
> cannot validate source in validation time.
How's it any different from anyelement?
regards, tom lane
On Thu, Sep 10, 2009 at 3:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Thu, Sep 10, 2009 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> 2. Come up with some way to do the equivalent of "variadic any[]", >>> ie, a variable number of not-all-the-same-type arguments. (This isn't >>> just a type-system problem, there's also the question of how the type >>> information would be passed at runtime. IIRC we have a solution at the >>> C level but not for PLs.) > >> This also seems like a good idea. Will pg_typeof() work for PL/pgsql? > > pg_typeof() applied to what? The existing approach assumes we can make > an array out of the variadic parameters, which isn't going to be the > case here. Oh. For some reason I thought that would still be possible. I don't think it's worth designing a whole new PL/pgsql language construct just to support this feature. Given that this should be a relatively unusual type of function, asking people to write them in C doesn't seem unreasonable. ...Robert
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: >>> 1. Allow the existing "any" pseudotype as an input argument type for PLs. >>> (AFAICS this is simple and painless; about the only question is whether >>> we want to keep using the name "any", which because of conflicting with >>> a reserved word would always need the double quotes.) > >> I thing so this is possible - I see only one critical point - you >> cannot validate source in validation time. > > How's it any different from anyelement? true, if I remember well, there is substitution from anyelement to int? maybe from this perspective can be good to separate polymorphic types to some kinds: any - really unknown type - there is possible only check on null or not null (and maybe some basic operations). anytext - any value (substituted to text) in validation time anynumeric - any value (substitued to integer) in validation time. regards Pavel Stehule > > regards, tom lane >
2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> I don't afraid about crashing. Simply I have not idea what sql
>> sprintf's behave in case:
>
>> SELECT sprintf('some %s', 10)
>
> That one I don't think is hard --- coerce the input type to text and
> print the string.
>
>> SELECT sprintf('some %d', 10::mycustomtype)
>
> For the formats that presume an integer or float input in C, perhaps
> we could coerce to numeric (failing if that fails) and then print
> appropriately. Or maybe int or float8 would be more appropriate
> conversion targets.
it's possible - so format tags doesn't mean data type, but it means
"try to drow it as type" - etc invisible explicit casting. It could
work, but it doesn't look like SQL.
regards
Pavel Stehule
>
> regards, tom lane
>
On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote: > On Thu, Sep 10, 2009 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Robert Haas <robertmhaas@gmail.com> writes: > >> The major downside of such a system is that every place where we now > >> count on being able to store a type in a fixed-size field would need > >> to be touched. I don't believe that the overall slowdown in parsing > >> time would be significant, but I do think it would be a massive, > >> highly invasive, highly destabilizing patch. For the level of pain > >> involved, there might be better uses of our time, > > > > Yeah, that's exactly the problem. > > > > I am not sure that we really *have to* have a non-OID-based type > > representation though. We have managed to have composite types without > > that, and I don't see why something similar would not work for > > functional types. > > It might be possible to make it work, but it's likely to create a lot > of bloat in pg_type, and will make it very difficult to implement > features such as anonymous functions (i.e. LAMBDA). For functions, anonymous does not mean "impossible to identify" ;) If it is something (semi)-permanent we should store it in pg_type and id it by oid, if it is really, really transient (say a closure generated upper in the function chain) we can probably assign it some kind of temporary, per-process oid for the duration of its existence > I think it's > further embedding a not-particularly-great design decision. We've > already patched around the insufficiency of representing types as > 32-bit integers for types by adding typmods, but that solution is > incompletely implemented (as exemplified by your comments below under > #4, and I don't think that's the only example) and doesn't handle all > the interesting cases. Maybe we should move in the direction of > having some kind of semi-opaque Type object that for now can contain a > typid and typmod, and always compare types by using some abstract > function that operates on Types. here we still could reference this Type object by a 32 bit integer, which points to either persistent or temporary "pg_type row" The trick is to look up (typeid,typmod) pairs in some hashtable and not generate duplicates. > > > But that's all well beyond the immediate problem, which is whether we > > need something more flexible than "anyelement". ISTM we had these > > not-all-mutually-exclusive ideas on the table: > > > 1. Allow the existing "any" pseudotype as an input argument type for PLs. > > (AFAICS this is simple and painless; about the only question is whether > > we want to keep using the name "any", which because of conflicting with > > a reserved word would always need the double quotes.) > > +1 from me. I could go either way on changing the name, but if we're > to do it, first we'll have to think of something better than "any", > which might not be so easy. we could also change parser and translate reserved word ANY to typename "any" . > > 2. Come up with some way to do the equivalent of "variadic any[]", > > ie, a variable number of not-all-the-same-type arguments. (This isn't > > just a type-system problem, there's also the question of how the type > > information would be passed at runtime. IIRC we have a solution at the > > C level but not for PLs.) > > This also seems like a good idea. Will pg_typeof() work for PL/pgsql? > I think if we can come up with a solution for PL/pgsql, it would be > reasonable to commit this, leaving the tidying up for any PLs for > which the right solution is non-obvious to someone who wants the > feature enough to propose an appropriate patch. > > > 3. Add anyelement2/anyarray2, and maybe also -3 and -4 while at it. > > > > 4. Instead of #3, allow anyelement(N), which is certainly more flexible > > than #3 but would require a much larger investment of work. (I'm > > uncertain whether attaching typmods to function arguments/results could > > have any interesting or unpleasant semantic side effects. It might be > > all good, or maybe not. It would definitely need some thought.) > > Of these two, I prefer #4, but I don't think #3 would be horrible either. This will introduce some tricky situations, as we can't always do function lookups in similar way to what we do now for real types. An example: f(a int, b text) and f(a text, b int) are two different functions which can be distinguished by their signature so are f(a int, b "any") and f(a "any", b int) and f(a "any", b "any") but f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1) seem to be different but actually are not, so we will need to handle multiple anyelementN types separately from ordinary types. > > 5. Various syntactic sugar to substitute for anyelement. (Not in favor > > of this myself, it seems to just complicate matters.) > > I agree; I don't think this solves any real problem. agreed, it does not solve the underlying problem, just may make it easier to understand and remember for users. ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they are much more SQL-like than needing to write "any" or anyelement(n) as argument type or return type -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
Hannu Krosing <hannu@2ndQuadrant.com> writes:
> On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote:
>> It might be possible to make it work, but it's likely to create a lot
>> of bloat in pg_type, and will make it very difficult to implement
>> features such as anonymous functions (i.e. LAMBDA).
> For functions, anonymous does not mean "impossible to identify" ;)
> If it is something (semi)-permanent we should store it in pg_type and id
> it by oid, if it is really, really transient (say a closure generated
> upper in the function chain) we can probably assign it some kind of
> temporary, per-process oid for the duration of its existence
Right. See what we do for anonymous composite types.
> we could also change parser and translate reserved word ANY to typename
> "any" .
ANY is a reserved word for good and sufficient reasons. "Change the
parser" is not an answer.
> f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1)
> seem to be different but actually are not, so we will need to handle
> multiple anyelementN types separately from ordinary types.
Excellent point. This would be an argument in favor of the typmod
approach (and not counting typmod as something that makes two functions
distinct...)
regards, tom lane
On Thu, 2009-09-10 at 21:30 +0200, Pavel Stehule wrote: > 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: > > Pavel Stehule <pavel.stehule@gmail.com> writes: > >> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: > >>> 1. Allow the existing "any" pseudotype as an input argument type for PLs. > >>> (AFAICS this is simple and painless; about the only question is whether > >>> we want to keep using the name "any", which because of conflicting with > >>> a reserved word would always need the double quotes.) > > > >> I thing so this is possible - I see only one critical point - you > >> cannot validate source in validation time. > > > > How's it any different from anyelement? > > true, if I remember well, there is substitution from anyelement to int? > > maybe from this perspective can be good to separate polymorphic types > to some kinds: > > any - really unknown type - there is possible only check on null or > not null (and maybe some basic operations). > anytext - any value (substituted to text) in validation time > anynumeric - any value (substitued to integer) in validation time. I think that way madness lies. then we should have anyXXX types for almost any subsets of types anytime , anygeom, anypointpair, anymorethantwopaintgeom, etc... better have a (possibility of) validation at compile time and validation/error-throwing at runtime - the latter is needed anyway. Unless we are going to implement CHECK constraints for function arguments and then use constraint exclusion for selecting the correct function ;) > regards > Pavel Stehule > > > > > regards, tom lane > > >
On Thu, 2009-09-10 at 21:35 +0200, Pavel Stehule wrote:
> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> > Pavel Stehule <pavel.stehule@gmail.com> writes:
> >> I don't afraid about crashing. Simply I have not idea what sql
> >> sprintf's behave in case:
> >
> >> SELECT sprintf('some %s', 10)
> >
> > That one I don't think is hard --- coerce the input type to text and
> > print the string.
> >
> >> SELECT sprintf('some %d', 10::mycustomtype)
> >
> > For the formats that presume an integer or float input in C, perhaps
> > we could coerce to numeric (failing if that fails) and then print
> > appropriately. Or maybe int or float8 would be more appropriate
> > conversion targets.
>
> it's possible - so format tags doesn't mean data type, but it means
> "try to drow it as type" - etc invisible explicit casting.
what is the difference between these two ?
> It could work, but it doesn't look like SQL.
but we do it all over the place if another type is needed and CAST
exists for getting it
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability Services, Consulting and Training
On Sep 10, 2009, at 12:10 PM, Tom Lane wrote:
>> SELECT sprintf('some %d', 10::mycustomtype)
>
> For the formats that presume an integer or float input in C, perhaps
> we could coerce to numeric (failing if that fails) and then print
> appropriately. Or maybe int or float8 would be more appropriate
> conversion targets.
Don't forget type categories, which could dictate useful defaults.
Best,
David
2009/9/10 Hannu Krosing <hannu@krosing.net>: > On Thu, 2009-09-10 at 21:30 +0200, Pavel Stehule wrote: >> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: >> > Pavel Stehule <pavel.stehule@gmail.com> writes: >> >> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>: >> >>> 1. Allow the existing "any" pseudotype as an input argument type for PLs. >> >>> (AFAICS this is simple and painless; about the only question is whether >> >>> we want to keep using the name "any", which because of conflicting with >> >>> a reserved word would always need the double quotes.) >> > >> >> I thing so this is possible - I see only one critical point - you >> >> cannot validate source in validation time. >> > >> > How's it any different from anyelement? >> >> true, if I remember well, there is substitution from anyelement to int? >> >> maybe from this perspective can be good to separate polymorphic types >> to some kinds: >> >> any - really unknown type - there is possible only check on null or >> not null (and maybe some basic operations). >> anytext - any value (substituted to text) in validation time >> anynumeric - any value (substitued to integer) in validation time. > > I think that way madness lies. > > then we should have anyXXX types for almost any subsets of types > > anytime , anygeom, anypointpair, anymorethantwopaintgeom, etc... true :( > > better have a (possibility of) validation at compile time and > validation/error-throwing at runtime - the latter is needed anyway. > I have very bad experience with late validation - like plpgsql did. It could to throw some exception too late (in production) - so this is some way, where we have to be carefully. It easy to write dynamic system - but this system should be dangerous in production. When I started with PostgreSQL I disliked hard typing system, now I love it - lot of things are predictable. > Unless we are going to implement CHECK constraints for function > arguments and then use constraint exclusion for selecting the correct > function ;) > >> regards >> Pavel Stehule >> >> > >> > regards, tom lane >> > >> > >
2009/9/10 Hannu Krosing <hannu@2ndquadrant.com>:
> On Thu, 2009-09-10 at 21:35 +0200, Pavel Stehule wrote:
>> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
>> > Pavel Stehule <pavel.stehule@gmail.com> writes:
>> >> I don't afraid about crashing. Simply I have not idea what sql
>> >> sprintf's behave in case:
>> >
>> >> SELECT sprintf('some %s', 10)
>> >
>> > That one I don't think is hard --- coerce the input type to text and
>> > print the string.
>> >
>> >> SELECT sprintf('some %d', 10::mycustomtype)
>> >
>> > For the formats that presume an integer or float input in C, perhaps
>> > we could coerce to numeric (failing if that fails) and then print
>> > appropriately. Or maybe int or float8 would be more appropriate
>> > conversion targets.
>>
>> it's possible - so format tags doesn't mean data type, but it means
>> "try to drow it as type" - etc invisible explicit casting.
>
>
> what is the difference between these two ?
first is coming from C and has C semantic - there is only one possible
tag (without binary compatible types) - you cannot use %s for numbers,
and %d for strings is some specific value.
sprintf("%d", "10") - show address of static string "10"
second is Tom's proposal. More dynamic. Tag specify target type.
so sprintf('%d', '10') show 10 with possible width manipulation operations
Pavel
>
>> It could work, but it doesn't look like SQL.
>
> but we do it all over the place if another type is needed and CAST
> exists for getting it
>
> --
> Hannu Krosing http://www.2ndQuadrant.com
> PostgreSQL Scalability and Availability
> Services, Consulting and Training
>
>
>
There is actualised version, for people who are interested on it.
Minimally it should be sample of variadic "any" function for playing.
Don't afraid, I don't plan to send it to commit fest.
regards
Pavel
2009/9/10 Alvaro Herrera <alvherre@commandprompt.com>:
> Pavel Stehule escribió:
>
>> Please, try to compile and run sprintf function from attachment
>
> There's a minor bug in the comparison to PG_NARGS() inside the loop,
> fixed in this version.
>
> The one problem I have with this is that if the format string does not
> contain any % (and thus there is no extra argument), it errors out:
>
> alvherre=# select text_format('ouch');
> ERROR: function text_format(unknown) does not exist
> LÍNEA 1: select text_format('ouch');
> ^
> SUGERENCIA: No function matches the given name and argument types. You might need to add explicit type casts.
>
> AFAICS fixing this would require a second pg_proc entry for this
> function.
>
>
> alvherre=# select text_format('% was % at % and said % % times', 'Pavel'::text, 'here'::unknown, now(),
row('a','b','c'),'{42}'::int[]);
> text_format
> -----------------------------------------------------------------------------
> Pavel was here at 2009-09-10 13:12:09.054653-04 and said (a,b,c) {42} times
> (1 fila)
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
Attachment
On Thu, 2009-09-10 at 22:15 +0200, Pavel Stehule wrote:
> 2009/9/10 Hannu Krosing <hannu@2ndquadrant.com>:
> > On Thu, 2009-09-10 at 21:35 +0200, Pavel Stehule wrote:
> >> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> >> > Pavel Stehule <pavel.stehule@gmail.com> writes:
> >> >> I don't afraid about crashing. Simply I have not idea what sql
> >> >> sprintf's behave in case:
> >> >
> >> >> SELECT sprintf('some %s', 10)
> >> >
> >> > That one I don't think is hard --- coerce the input type to text and
> >> > print the string.
> >> >
> >> >> SELECT sprintf('some %d', 10::mycustomtype)
> >> >
> >> > For the formats that presume an integer or float input in C, perhaps
> >> > we could coerce to numeric (failing if that fails) and then print
> >> > appropriately. Or maybe int or float8 would be more appropriate
> >> > conversion targets.
> >>
> >> it's possible - so format tags doesn't mean data type, but it means
> >> "try to drow it as type" - etc invisible explicit casting.
> >
> >
> > what is the difference between these two ?
>
> first is coming from C and has C semantic - there is only one possible
> tag (without binary compatible types) - you cannot use %s for numbers,
> and %d for strings is some specific value.
Similar functionality in python allows %s for any type (it will just get
string representation of the value), but not vice-versa:
>>> '%s' % 10
'10'
>>> '%d' % 10
'10'
>>> '%d' % '10'
Traceback (most recent call last): File "<stdin>", line 1, in <module>
TypeError: %d format: a number is required, not str
More restrictive than Tom's proposal.
> sprintf("%d", "10") - show address of static string "10"
>
> second is Tom's proposal. More dynamic. Tag specify target type.
>
> so sprintf('%d', '10') show 10 with possible width manipulation operations
this would be interesting for cases like
sprintf('%.2f', '3.1415927')
but I'd frefer here python-like sematics, where you need to make
explicit casts, i.e.
sprintf('%.2f', '3.1415927'::float)
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability Services, Consulting and Training
Hannu Krosing <hannu@2ndQuadrant.com> writes: >> > 5. Various syntactic sugar to substitute for anyelement. (Not in favor >> > of this myself, it seems to just complicate matters.) >> >> I agree; I don't think this solves any real problem. > > agreed, it does not solve the underlying problem, just may make it > easier to understand and remember for users. Well, depends. I'm not convinced that abusing typmods for solving this is a good idea, but it's just feeling. Having the same syntax covers different semantics depending on the context is like interpreting data in a column in different ways, and is on Josh's list of things to do if you wanna wreck your database. > f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1) > > seem to be different but actually are not, so we will need to handle > multiple anyelementN types separately from ordinary types. Here for example you see that typmod on anyelement would mean something entirely different from typmod on, say, numeric. This looks like a ugly hack. > ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they > are much more SQL-like than needing to write "any" or anyelement(n) as > argument type or return type Not only it looks SQL'ish, but it'll be easier to document and use. And it won't taint typmods, which still need a refactor to better handle PostGIS needs, btw... Now there's still the issue of having first class functions: that means we'd be able to store them, have anonymous (lambda) functions (see the DO command discussion), higher order function, and open the road to generic function support. All that jazz is being pushed back with the very natural counter argument: what good is there for having this in your SQL database? So for people on the list who really want to push towards those things, I suppose providing realistic use cases (how it would simplify this hack you're maintaining in production) would allow for better exchanges :) Regards, -- dim
* Pavel Stehule <pavel.stehule@gmail.com> [090910 16:16]:
> first is coming from C and has C semantic - there is only one possible
> tag (without binary compatible types) - you cannot use %s for numbers,
> and %d for strings is some specific value.
>
> sprintf("%d", "10") - show address of static string "10"
>
> second is Tom's proposal. More dynamic. Tag specify target type.
>
> so sprintf('%d', '10') show 10 with possible width manipulation operations
Just to make the task that much harder, if PostgreSQL is going to have a
sprintf (in core, or contrib), I *really* hope it's a real sprintf,
supporting everything, like: $m positional notation * width argument All the flags [#0- +'] (I as a bonus) field
width. presision
And you're going to want to make sure you support all the regular
conversion specifiers (d/i/o/u/x/X/e/E/f/F/g/G/p/n/c/s)...
How to deal with types conflicting with the conversion specifier is
going to be something necessary to look at (And just crashing a-la-C
probably isn't nice).
If you're making sprintf, then it's going to need to be C-like, and it's
going to be a lot of tedious conversion/formating... Of course, I'ld
love to see it, because I can forever then forget about all that tedious
formatting in PosgreSQL...
a.
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
On Thu, 2009-09-10 at 15:49 -0400, Tom Lane wrote: > Hannu Krosing <hannu@2ndQuadrant.com> writes: > > On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote: > >> It might be possible to make it work, but it's likely to create a lot > >> of bloat in pg_type, and will make it very difficult to implement > >> features such as anonymous functions (i.e. LAMBDA). > > > For functions, anonymous does not mean "impossible to identify" ;) > > > If it is something (semi)-permanent we should store it in pg_type and id > > it by oid, if it is really, really transient (say a closure generated > > upper in the function chain) we can probably assign it some kind of > > temporary, per-process oid for the duration of its existence > > Right. See what we do for anonymous composite types. > > > > we could also change parser and translate reserved word ANY to typename > > "any" . > > ANY is a reserved word for good and sufficient reasons. "Change the > parser" is not an answer. I suspect that alt least in some early SQL parsers all type names were reserved. Or do you see a possible conflict here ? What way can ANY be used in function type definition ? > > f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1) > > > seem to be different but actually are not, so we will need to handle > > multiple anyelementN types separately from ordinary types. > > Excellent point. This would be an argument in favor of the typmod > approach (and not counting typmod as something that makes two functions > distinct...) this seems like an elegant solution. OTOH we still have a more compliacted case of f(a anyelement1, b anyelement1, c anyelement2) and f(a anyelement1, b anyelement2, c anyelement2) which may not be solved by non-differentiating typmod > > regards, tom lane -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
2009/9/10 Dimitri Fontaine <dfontaine@hi-media.com>: > Hannu Krosing <hannu@2ndQuadrant.com> writes: >>> > 5. Various syntactic sugar to substitute for anyelement. (Not in favor >>> > of this myself, it seems to just complicate matters.) >>> >>> I agree; I don't think this solves any real problem. >> >> agreed, it does not solve the underlying problem, just may make it >> easier to understand and remember for users. > > Well, depends. I'm not convinced that abusing typmods for solving this > is a good idea, but it's just feeling. Having the same syntax covers > different semantics depending on the context is like interpreting data > in a column in different ways, and is on Josh's list of things to do if > you wanna wreck your database. > >> f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1) >> >> seem to be different but actually are not, so we will need to handle >> multiple anyelementN types separately from ordinary types. > > Here for example you see that typmod on anyelement would mean something > entirely different from typmod on, say, numeric. > > This looks like a ugly hack. > >> ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they >> are much more SQL-like than needing to write "any" or anyelement(n) as >> argument type or return type > it's less confusing, than abstract types. And really nicer. SQL have to be readable for non hard developers too. And this is readable. Much more than anylement(1) or anyelement(n). > Not only it looks SQL'ish, but it'll be easier to document and use. And > it won't taint typmods, which still need a refactor to better handle > PostGIS needs, btw... > > > Now there's still the issue of having first class functions: that means > we'd be able to store them, have anonymous (lambda) functions (see the > DO command discussion), higher order function, and open the road to > generic function support. All that jazz is being pushed back with the > very natural counter argument: what good is there for having this in > your SQL database? > > So for people on the list who really want to push towards those things, > I suppose providing realistic use cases (how it would simplify this hack > you're maintaining in production) would allow for better exchanges :) I am fully agree regards Pavel > > Regards, > -- > dim >
On Thu, Sep 10, 2009 at 4:38 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote: > On Thu, 2009-09-10 at 15:49 -0400, Tom Lane wrote: >> Hannu Krosing <hannu@2ndQuadrant.com> writes: >> > On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote: >> >> It might be possible to make it work, but it's likely to create a lot >> >> of bloat in pg_type, and will make it very difficult to implement >> >> features such as anonymous functions (i.e. LAMBDA). >> >> > For functions, anonymous does not mean "impossible to identify" ;) >> >> > If it is something (semi)-permanent we should store it in pg_type and id >> > it by oid, if it is really, really transient (say a closure generated >> > upper in the function chain) we can probably assign it some kind of >> > temporary, per-process oid for the duration of its existence >> >> Right. See what we do for anonymous composite types. >> >> >> > we could also change parser and translate reserved word ANY to typename >> > "any" . >> >> ANY is a reserved word for good and sufficient reasons. "Change the >> parser" is not an answer. > > I suspect that alt least in some early SQL parsers all type names were reserved. > > Or do you see a possible conflict here ? > > What way can ANY be used in function type definition ? Perhaps you should try changing ANY to a non-reserved word in the parser and see what happens. If you come up with a way to resolve the shift/reduce and/or reduce/reduce conflicts that will probably result, submit a patch. ...Robert
On Thu, Sep 10, 2009 at 4:43 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2009/9/10 Dimitri Fontaine <dfontaine@hi-media.com>: >> Hannu Krosing <hannu@2ndQuadrant.com> writes: >>>> > 5. Various syntactic sugar to substitute for anyelement. (Not in favor >>>> > of this myself, it seems to just complicate matters.) >>>> >>>> I agree; I don't think this solves any real problem. >>> >>> agreed, it does not solve the underlying problem, just may make it >>> easier to understand and remember for users. >> >> Well, depends. I'm not convinced that abusing typmods for solving this >> is a good idea, but it's just feeling. Having the same syntax covers >> different semantics depending on the context is like interpreting data >> in a column in different ways, and is on Josh's list of things to do if >> you wanna wreck your database. >> >>> f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1) >>> >>> seem to be different but actually are not, so we will need to handle >>> multiple anyelementN types separately from ordinary types. >> >> Here for example you see that typmod on anyelement would mean something >> entirely different from typmod on, say, numeric. >> >> This looks like a ugly hack. >> >>> ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they >>> are much more SQL-like than needing to write "any" or anyelement(n) as >>> argument type or return type >> > > it's less confusing, than abstract types. And really nicer. > > SQL have to be readable for non hard developers too. And this is > readable. Much more than anylement(1) or anyelement(n). > >> Not only it looks SQL'ish, but it'll be easier to document and use. And >> it won't taint typmods, which still need a refactor to better handle >> PostGIS needs, btw... >> >> >> Now there's still the issue of having first class functions: that means >> we'd be able to store them, have anonymous (lambda) functions (see the >> DO command discussion), higher order function, and open the road to >> generic function support. All that jazz is being pushed back with the >> very natural counter argument: what good is there for having this in >> your SQL database? >> >> So for people on the list who really want to push towards those things, >> I suppose providing realistic use cases (how it would simplify this hack >> you're maintaining in production) would allow for better exchanges :) > > I am fully agree So submit it as a follow-on patch and it can be discussed. It still has to translate into some other construct (like anyelement2 or anyelement(2)) internally, so we might as well do those first before worrying about the rest of it. ...Robert
On Thu, 2009-09-10 at 16:48 -0400, Robert Haas wrote: > On Thu, Sep 10, 2009 at 4:38 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote: > > On Thu, 2009-09-10 at 15:49 -0400, Tom Lane wrote: > >> Hannu Krosing <hannu@2ndQuadrant.com> writes: > >> > On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote: > >> >> It might be possible to make it work, but it's likely to create a lot > >> >> of bloat in pg_type, and will make it very difficult to implement > >> >> features such as anonymous functions (i.e. LAMBDA). > >> > >> > For functions, anonymous does not mean "impossible to identify" ;) > >> > >> > If it is something (semi)-permanent we should store it in pg_type and id > >> > it by oid, if it is really, really transient (say a closure generated > >> > upper in the function chain) we can probably assign it some kind of > >> > temporary, per-process oid for the duration of its existence > >> > >> Right. See what we do for anonymous composite types. > >> > >> > >> > we could also change parser and translate reserved word ANY to typename > >> > "any" . > >> > >> ANY is a reserved word for good and sufficient reasons. "Change the > >> parser" is not an answer. > > > > I suspect that alt least in some early SQL parsers all type names were reserved. > > > > Or do you see a possible conflict here ? > > > > What way can ANY be used in function type definition ? > > Perhaps you should try changing ANY to a non-reserved word in the > parser and see what happens. If you come up with a way to resolve the > shift/reduce and/or reduce/reduce conflicts that will probably result, > submit a patch. I don't want it to be a non-reserved word. What I want is that this reserved word can be used in function argument and return type definitions with special meaning like reserver word FROM , which can be used in two different meanings like this SELECT substring(fielda FROM myregex') FROM mytable; > ...Robert -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
2009/9/10 Robert Haas <robertmhaas@gmail.com>: > On Thu, Sep 10, 2009 at 4:43 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2009/9/10 Dimitri Fontaine <dfontaine@hi-media.com>: >>> Hannu Krosing <hannu@2ndQuadrant.com> writes: >>>>> > 5. Various syntactic sugar to substitute for anyelement. (Not in favor >>>>> > of this myself, it seems to just complicate matters.) >>>>> >>>>> I agree; I don't think this solves any real problem. >>>> >>>> agreed, it does not solve the underlying problem, just may make it >>>> easier to understand and remember for users. >>> >>> Well, depends. I'm not convinced that abusing typmods for solving this >>> is a good idea, but it's just feeling. Having the same syntax covers >>> different semantics depending on the context is like interpreting data >>> in a column in different ways, and is on Josh's list of things to do if >>> you wanna wreck your database. >>> >>>> f(a anyelement1, b anyelement2) and f(a anyelement2, b anyelement1) >>>> >>>> seem to be different but actually are not, so we will need to handle >>>> multiple anyelementN types separately from ordinary types. >>> >>> Here for example you see that typmod on anyelement would mean something >>> entirely different from typmod on, say, numeric. >>> >>> This looks like a ugly hack. >>> >>>> ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they >>>> are much more SQL-like than needing to write "any" or anyelement(n) as >>>> argument type or return type >>> >> >> it's less confusing, than abstract types. And really nicer. >> >> SQL have to be readable for non hard developers too. And this is >> readable. Much more than anylement(1) or anyelement(n). >> >>> Not only it looks SQL'ish, but it'll be easier to document and use. And >>> it won't taint typmods, which still need a refactor to better handle >>> PostGIS needs, btw... >>> >>> >>> Now there's still the issue of having first class functions: that means >>> we'd be able to store them, have anonymous (lambda) functions (see the >>> DO command discussion), higher order function, and open the road to >>> generic function support. All that jazz is being pushed back with the >>> very natural counter argument: what good is there for having this in >>> your SQL database? >>> >>> So for people on the list who really want to push towards those things, >>> I suppose providing realistic use cases (how it would simplify this hack >>> you're maintaining in production) would allow for better exchanges :) >> >> I am fully agree > > So submit it as a follow-on patch and it can be discussed. It still > has to translate into some other construct (like anyelement2 or > anyelement(2)) internally, so we might as well do those first before > worrying about the rest of it. I sent more general patch. It's irony. I thing, so syntax ANY [TYPE] and SAME AS [TYPE OF] is good, very good. But it works only for functions with fixed argument numbers, so real usage isn't big. Minimum we need third kind - COMMON TYPE OF (variables). This patch will be very large - it needs big change of pg_proc :( Regards Pavel > > ...Robert >
2009/9/10 Hannu Krosing <hannu@2ndquadrant.com>: > On Thu, 2009-09-10 at 16:48 -0400, Robert Haas wrote: >> On Thu, Sep 10, 2009 at 4:38 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote: >> > On Thu, 2009-09-10 at 15:49 -0400, Tom Lane wrote: >> >> Hannu Krosing <hannu@2ndQuadrant.com> writes: >> >> > On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote: >> >> >> It might be possible to make it work, but it's likely to create a lot >> >> >> of bloat in pg_type, and will make it very difficult to implement >> >> >> features such as anonymous functions (i.e. LAMBDA). >> >> >> >> > For functions, anonymous does not mean "impossible to identify" ;) >> >> >> >> > If it is something (semi)-permanent we should store it in pg_type and id >> >> > it by oid, if it is really, really transient (say a closure generated >> >> > upper in the function chain) we can probably assign it some kind of >> >> > temporary, per-process oid for the duration of its existence >> >> >> >> Right. See what we do for anonymous composite types. >> >> >> >> >> >> > we could also change parser and translate reserved word ANY to typename >> >> > "any" . >> >> >> >> ANY is a reserved word for good and sufficient reasons. "Change the >> >> parser" is not an answer. >> > >> > I suspect that alt least in some early SQL parsers all type names were reserved. >> > >> > Or do you see a possible conflict here ? >> > >> > What way can ANY be used in function type definition ? >> >> Perhaps you should try changing ANY to a non-reserved word in the >> parser and see what happens. If you come up with a way to resolve the >> shift/reduce and/or reduce/reduce conflicts that will probably result, >> submit a patch. > > I don't want it to be a non-reserved word. > > What I want is that this reserved word can be used in function argument > and return type definitions with special meaning > > like reserver word FROM , which can be used in two different meanings > like this > > SELECT substring(fielda FROM myregex') FROM mytable; > I thing, so this is possible - this is some steps from pseudotypes to some function's descriptors (metadata collections). regards Pavel > > > >> ...Robert > -- > Hannu Krosing http://www.2ndQuadrant.com > PostgreSQL Scalability and Availability > Services, Consulting and Training > > >
On Sep 10, 2009, at 2:08 PM, Hannu Krosing wrote: >> Perhaps you should try changing ANY to a non-reserved word in the >> parser and see what happens. If you come up with a way to resolve >> the >> shift/reduce and/or reduce/reduce conflicts that will probably >> result, >> submit a patch. > > I don't want it to be a non-reserved word. I say we use "whatever" (quotes not required). David
On Thu, Sep 10, 2009 at 5:08 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote: > On Thu, 2009-09-10 at 16:48 -0400, Robert Haas wrote: >> On Thu, Sep 10, 2009 at 4:38 PM, Hannu Krosing <hannu@2ndquadrant.com> wrote: >> > On Thu, 2009-09-10 at 15:49 -0400, Tom Lane wrote: >> >> Hannu Krosing <hannu@2ndQuadrant.com> writes: >> >> > On Thu, 2009-09-10 at 15:06 -0400, Robert Haas wrote: >> >> >> It might be possible to make it work, but it's likely to create a lot >> >> >> of bloat in pg_type, and will make it very difficult to implement >> >> >> features such as anonymous functions (i.e. LAMBDA). >> >> >> >> > For functions, anonymous does not mean "impossible to identify" ;) >> >> >> >> > If it is something (semi)-permanent we should store it in pg_type and id >> >> > it by oid, if it is really, really transient (say a closure generated >> >> > upper in the function chain) we can probably assign it some kind of >> >> > temporary, per-process oid for the duration of its existence >> >> >> >> Right. See what we do for anonymous composite types. >> >> >> >> >> >> > we could also change parser and translate reserved word ANY to typename >> >> > "any" . >> >> >> >> ANY is a reserved word for good and sufficient reasons. "Change the >> >> parser" is not an answer. >> > >> > I suspect that alt least in some early SQL parsers all type names were reserved. >> > >> > Or do you see a possible conflict here ? >> > >> > What way can ANY be used in function type definition ? >> >> Perhaps you should try changing ANY to a non-reserved word in the >> parser and see what happens. If you come up with a way to resolve the >> shift/reduce and/or reduce/reduce conflicts that will probably result, >> submit a patch. > > I don't want it to be a non-reserved word. > > What I want is that this reserved word can be used in function argument > and return type definitions with special meaning > > like reserver word FROM , which can be used in two different meanings > like this > > SELECT substring(fielda FROM myregex') FROM mytable; OK so implement it. ...Robert
Aidan Van Dyk escribió: > Just to make the task that much harder, if PostgreSQL is going to have a > sprintf (in core, or contrib), I *really* hope it's a real sprintf, > supporting everything, like: > $m positional notation > * width argument > All the flags [#0- +'] (I as a bonus) > field width . presision > > And you're going to want to make sure you support all the regular > conversion specifiers (d/i/o/u/x/X/e/E/f/F/g/G/p/n/c/s)... Is this really all that hard? I'm thinking it could be implemented by using the real C sprintf underneath, passing one % specifier and its corresponding parameter at a time, coerced to whatever the conversion specifier specifies. The only thing that breaks this idea is the $n positional specifiers, I think. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
2009/9/11 Alvaro Herrera <alvherre@commandprompt.com>: > Aidan Van Dyk escribió: > >> Just to make the task that much harder, if PostgreSQL is going to have a >> sprintf (in core, or contrib), I *really* hope it's a real sprintf, >> supporting everything, like: >> $m positional notation >> * width argument >> All the flags [#0- +'] (I as a bonus) >> field width . presision >> >> And you're going to want to make sure you support all the regular >> conversion specifiers (d/i/o/u/x/X/e/E/f/F/g/G/p/n/c/s)... > > Is this really all that hard? I'm thinking it could be implemented by > using the real C sprintf underneath, passing one % specifier and its > corresponding parameter at a time, coerced to whatever the conversion > specifier specifies. What conversion between PostgreSQL datatypes and C types? PostgreSQL missing infrastructure for it. Pavel > > The only thing that breaks this idea is the $n positional specifiers, I > think. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
* Alvaro Herrera <alvherre@commandprompt.com> [090910 23:32]: > Is this really all that hard? I'm thinking it could be implemented by > using the real C sprintf underneath, passing one % specifier and its > corresponding parameter at a time, coerced to whatever the conversion > specifier specifies. It's not "hard", but please, don't break this, to make it more "not hard": > The only thing that breaks this idea is the $n positional specifiers, I > think. And also, please work for user-defined types (meaning you need to use the type and catalog system to lookup coercions, not hard-code anything... l-) It's doable, but it's going got be a lot of explicit casting and coercion, and going to require a lot of documentation and error states... Remember, users using sprintf are really going to want it to act exactly as it would if they were using C, minus the crash part. a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
On Thu, 2009-09-10 at 19:52 +0200, Pavel Stehule wrote:
> 2009/9/10 Tom Lane <tgl@sss.pgh.pa.us>:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> >> alvherre=# select text_format('% was % at % and said % % times', 'Pavel'::text, 'here'::unknown, now(),
row('a','b','c'),'{42}'::int[]);
> >> text_format
> >> -----------------------------------------------------------------------------
> >> Pavel was here at 2009-09-10 13:12:09.054653-04 and said (a,b,c) {42} times
> >> (1 fila)
> >
> > Is that what's being proposed? That pretty much sucks --- it's just
> > another way of concatenating some strings. I thought the idea was to
> > provide the same power as sprintf, eg field width controls, numeric
> > formatting options, etc.
> >
>
> I thing so this is enough - we can get simply message text - like
> raise notice statement. I thing so simple and clean function has more
> usability than heavy real sprintf function. We (c coders) are old
> dinosaurs - but nobody else knows what sprintf function does.
They probably do, as at least PHP and perl have also (s)printf
functions, probably many others as well. But most likely each of them
has a slightly different syntax.
> I thing so the name only "format" is good, it's short. If you need
> some other formating, just you can use to_char function.
yes, format(...) is generic enough that people won't expect it to
confirm to their favorite languages version of printf.
> Pavel
>
> > regards, tom lane
> >
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability Services, Consulting and Training
Kevin Grittner escribió:
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> > what is more readable?
> >
> > select 'i=' || i || ', b=' || b || ', c=' || c ..
> >
> > or
> >
> > select format('i=%, b=%, c=%', i, b, c ..)
>
> Seriously, those are about dead even for me. The concatenation
> might have a slight edge, particularly since I have the option, if
> it gets out of hand, to do:
>
> select 'i=' || i
> || ', b=' || b
> || ', c=' || c
> ..
That barely works for me, and then only because it's a trivial example.
In real uses it's never that clear-cut, and the format version is a lot
better than the || alternative.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> wrote: > the format version is a lot better than the || alternative. Well, if you're trying to tell me what is easier for me to read, you're probably wrong. I won't presume to try to tell you what you find easier to read. I think the main benefit of a sprintf type function for PostgreSQL is in the formatting (setting length, scale, alignment), not in making concatenation more pretty. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> I think the main benefit of a sprintf type function for PostgreSQL is
> in the formatting (setting length, scale, alignment), not in making
> concatenation more pretty.
Exactly, which is why I'm so distressed that this proposal not only
hasn't got that, but is designed so that it's impossible to add it
later.
regards, tom lane
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Is this really all that hard? I'm thinking it could be implemented by
> using the real C sprintf underneath, passing one % specifier and its
> corresponding parameter at a time, coerced to whatever the conversion
> specifier specifies.
The only disadvantage I can see of that is that it would lose precision
for NUMERIC. I'd really like to be able to write "%300.100f" and have it
Do The Right Thing with a 300-digit numeric input.
> The only thing that breaks this idea is the $n positional specifiers, I
> think.
Yeah, that's a bit of a pain too. But we have the logic for that in
src/port/. It wouldn't be that much work to repurpose it. Actually,
since a SQL implementation wouldn't be constrained to read the actual
arguments left-to-right, you could probably simplify it a great deal.
regards, tom lane
On Fri, Sep 11, 2009 at 10:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> I think the main benefit of a sprintf type function for PostgreSQL is >> in the formatting (setting length, scale, alignment), not in making >> concatenation more pretty. > > Exactly, which is why I'm so distressed that this proposal not only > hasn't got that, but is designed so that it's impossible to add it > later. I like the idea of making concatenation more pretty, quite frankly. No one has really responded to Pavel's contention that this is what to_char() is for. Twice the code paths = twice the bugs, twice the places that have to be updated when some new feature is added, etc. On the other hand I don't really strongly object if someone else wants to do the work, either. I do think allowing for upward compatibility with future extensions is probably smart, regardless of how simple or complex the first version is. ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> I like the idea of making concatenation more pretty, quite frankly.
> No one has really responded to Pavel's contention that this is what
> to_char() is for.
[ shrug... ] I regard this as a prettier replacement for to_char.
That thing has got nothing whatsoever to recommend it, other than being
bug-compatible with Oracle.
regards, tom lane
On Fri, Sep 11, 2009 at 11:19 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Sep 11, 2009 at 10:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>> I think the main benefit of a sprintf type function for PostgreSQL is
>>> in the formatting (setting length, scale, alignment), not in making
>>> concatenation more pretty.
>>
>> Exactly, which is why I'm so distressed that this proposal not only
>> hasn't got that, but is designed so that it's impossible to add it
>> later.
>
> I like the idea of making concatenation more pretty, quite frankly.
> No one has really responded to Pavel's contention that this is what
> to_char() is for. Twice the code paths = twice the bugs, twice the
> places that have to be updated when some new feature is added, etc.
If you are going to use printf format codes, which is good and useful
being something of a standard, I'd call routine printf (not format)
and actually wrap vsnprintf. The format codes in printf have a very
specific meaning: converting native C types to arrays of characters.
I think that a postgresql implementation should do exactly that:
attempt to convert the passed in datum to the c type in question if
possible (erroring if no cast exists) and then pass it down. The idea
is we are not adding new formatting routines but using a very high
quality existing one...why reinvent the wheel?
so if you did: select printf('%s %3.1f', foo::box, bar::circle);
the box to char* cast would work (using the text cast) but the second
cast would fail unless the user added a cast to float. The code in
question is easy to imagine...parse the format string, and loop the
varargs using the appropriate looked up cast one by one...
merlin
On Fri, Sep 11, 2009 at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Is this really all that hard? I'm thinking it could be implemented by >> using the real C sprintf underneath, passing one % specifier and its >> corresponding parameter at a time, coerced to whatever the conversion >> specifier specifies. > > The only disadvantage I can see of that is that it would lose precision > for NUMERIC. I'd really like to be able to write "%300.100f" and have it > Do The Right Thing with a 300-digit numeric input. that could be simply worked around by formatting the numeric in sql and passing it to printf as %s. merlin
Merlin Moncure <mmoncure@gmail.com> writes:
> If you are going to use printf format codes, which is good and useful
> being something of a standard, I'd call routine printf (not format)
> and actually wrap vsnprintf. The format codes in printf have a very
> specific meaning: converting native C types to arrays of characters.
> I think that a postgresql implementation should do exactly that:
> attempt to convert the passed in datum to the c type in question if
> possible (erroring if no cast exists) and then pass it down.
I think this is a bit too restrictive. Aside from the issue of loss of
precision for NUMERIC, do we really want users to have to deal with the
fact that "long" doesn't mean the same thing on every platform? I don't
want the same SQL to work on some platforms and fail on others because
a particular datatype has a cast to int4 and not to int8, for instance.
We should certainly leverage the C library as much as we can for this,
but exposing users to every single idiosyncrasy of C is not quite the
right thing IMHO.
regards, tom lane
On Fri, Sep 11, 2009 at 12:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> If you are going to use printf format codes, which is good and useful >> being something of a standard, I'd call routine printf (not format) >> and actually wrap vsnprintf. The format codes in printf have a very >> specific meaning: converting native C types to arrays of characters. >> I think that a postgresql implementation should do exactly that: >> attempt to convert the passed in datum to the c type in question if >> possible (erroring if no cast exists) and then pass it down. > > I think this is a bit too restrictive. Aside from the issue of loss of > precision for NUMERIC, do we really want users to have to deal with the > fact that "long" doesn't mean the same thing on every platform? I don't > want the same SQL to work on some platforms and fail on others because > a particular datatype has a cast to int4 and not to int8, for instance. > > We should certainly leverage the C library as much as we can for this, > but exposing users to every single idiosyncrasy of C is not quite the > right thing IMHO. hmm. how about leaving the existing format codes alone and making some safer additional ones that we advice the user to use? It could probably be all fixed up in the vsnprintf layer. merlin
2009/9/11 Tom Lane <tgl@sss.pgh.pa.us>: > Merlin Moncure <mmoncure@gmail.com> writes: >> If you are going to use printf format codes, which is good and useful >> being something of a standard, I'd call routine printf (not format) >> and actually wrap vsnprintf. The format codes in printf have a very >> specific meaning: converting native C types to arrays of characters. >> I think that a postgresql implementation should do exactly that: >> attempt to convert the passed in datum to the c type in question if >> possible (erroring if no cast exists) and then pass it down. > > I think this is a bit too restrictive. Aside from the issue of loss of > precision for NUMERIC, do we really want users to have to deal with the > fact that "long" doesn't mean the same thing on every platform? I don't > want the same SQL to work on some platforms and fail on others because > a particular datatype has a cast to int4 and not to int8, for instance. > > We should certainly leverage the C library as much as we can for this, > but exposing users to every single idiosyncrasy of C is not quite the > right thing IMHO. > I am thinking so PostgreSQL sprintf function that isn't real sprintf function is really perfect idea. I see messages, sprintf doesn't support format correctly ... And I will have three sprintf functions, perl, c and postgres, ... still are you thinking, so this is good idea? regards Pavel Stehule > regards, tom lane >
On Sep 11, 2009, at 10:19 AM, Robert Haas wrote: > On Fri, Sep 11, 2009 at 10:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >>> I think the main benefit of a sprintf type function for >>> PostgreSQL is >>> in the formatting (setting length, scale, alignment), not in making >>> concatenation more pretty. >> >> Exactly, which is why I'm so distressed that this proposal not only >> hasn't got that, but is designed so that it's impossible to add it >> later. > > I like the idea of making concatenation more pretty, quite frankly. Speaking of concatenation... Something I find sorely missing in plpgsql is the ability to put variables inside of a string, ie: DECLARE v_table text := ... v_sql text; BEGIN v_sql := "SELECT * FROM $v_table"; Of course, I'm assuming that if it was easy to do that it would be done already... but I thought I'd just throw it out there. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Fri, Sep 11, 2009 at 11:43:32AM -0400, Merlin Moncure wrote:
>
> If you are going to use printf format codes, which is good and useful
> being something of a standard, I'd call routine printf (not format)
> and actually wrap vsnprintf. The format codes in printf have a very
> specific meaning: converting native C types to arrays of characters.
> I think that a postgresql implementation should do exactly that:
> attempt to convert the passed in datum to the c type in question if
> possible (erroring if no cast exists) and then pass it down. The idea
> is we are not adding new formatting routines but using a very high
> quality existing one...why reinvent the wheel?
>
> so if you did: select printf('%s %3.1f', foo::box, bar::circle);
> the box to char* cast would work (using the text cast) but the second
> cast would fail unless the user added a cast to float. The code in
> question is easy to imagine...parse the format string, and loop the
> varargs using the appropriate looked up cast one by one...
+1
-dg
--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.
decibel wrote: > > > Speaking of concatenation... > > Something I find sorely missing in plpgsql is the ability to put > variables inside of a string, ie: > > DECLARE > v_table text := ... > v_sql text; > BEGIN > v_sql := "SELECT * FROM $v_table"; > > Of course, I'm assuming that if it was easy to do that it would be > done already... but I thought I'd just throw it out there. > Then use a language that supports variable interpolation in strings, like plperl, plpythonu, plruby .... instead of plpgsql. cheers andrew
Hello > > ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they > are much more SQL-like than needing to write "any" or anyelement(n) as > argument type or return type > I looked on possibilities in gram.y and I thing, type identifiers "ANY TYPE" is possible without any problems (this should be synonym for "any"), "SAME AS" needs add "same" keyword to col_name_keywords , i.e. "same" is prohibited for function names - it should be a problem regards Pavel Stehule I found so pgparser provide some ref type syntax via % symbol. So we can use following syntax: CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b a%TYPE) RETURNS a%TYPE ... It is not pretty like SAME AS, but I am sure, so this is doable (parser knows it now) any other ideas? regards Pavel Stehule
On Sun, 2009-09-13 at 21:50 +0200, Pavel Stehule wrote: > Hello > > > > > ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they > > are much more SQL-like than needing to write "any" or anyelement(n) as > > argument type or return type > > > > I looked on possibilities in gram.y and I thing, type identifiers > > "ANY TYPE" is possible without any problems (this should be synonym for "any"), > "SAME AS" needs add "same" keyword to col_name_keywords , i.e. "same" > is prohibited for function names - it should be a problem > > regards > Pavel Stehule > > I found so pgparser provide some ref type syntax via % symbol. So we > can use following syntax: > > CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b a%TYPE) > RETURNS a%TYPE ... > > It is not pretty like SAME AS, but I am sure, so this is doable > (parser knows it now) > > any other ideas? Hmm, maybe try to make lexer recognize "SAME AS" as one token and then deal with other cases of 'name AS' ? Or make the syntax a little uglier, CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a) CREATE OR REPLACE FUNCTION foo(ANY TYPE, TYPE OF $1) and maybe try CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a) RETURNS ARRAY OF TYPE OF a instead of CREATE OR REPLACE FUNCTION foo(a anyelement, b anyelement) RETURNS anyarray > > regards > Pavel Stehule -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training
On Sep 12, 2009, at 5:54 PM, Andrew Dunstan wrote: > decibel wrote: >> Speaking of concatenation... >> >> Something I find sorely missing in plpgsql is the ability to put >> variables inside of a string, ie: >> >> DECLARE >> v_table text := ... >> v_sql text; >> BEGIN >> v_sql := "SELECT * FROM $v_table"; >> >> Of course, I'm assuming that if it was easy to do that it would be >> done already... but I thought I'd just throw it out there. >> > > Then use a language that supports variable interpolation in > strings, like plperl, plpythonu, plruby .... instead of plpgsql. Which makes executing SQL much, much harder. At least if we get sprintf dealing with strings might become a bit easier... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
2009/9/13 decibel <decibel@decibel.org>: > On Sep 12, 2009, at 5:54 PM, Andrew Dunstan wrote: >> >> decibel wrote: >>> >>> Speaking of concatenation... >>> >>> Something I find sorely missing in plpgsql is the ability to put >>> variables inside of a string, ie: >>> >>> DECLARE >>> v_table text := ... >>> v_sql text; >>> BEGIN >>> v_sql := "SELECT * FROM $v_table"; >>> >>> Of course, I'm assuming that if it was easy to do that it would be done >>> already... but I thought I'd just throw it out there. >>> >> >> Then use a language that supports variable interpolation in strings, like >> plperl, plpythonu, plruby .... instead of plpgsql. > > > Which makes executing SQL much, much harder. > > At least if we get sprintf dealing with strings might become a bit easier... This feature is nice - but very dangerous - it the most easy way how do vulnerable (on SQL injection) application! regards Pavel Stehule > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > >
2009/9/13 Hannu Krosing <hannu@2ndquadrant.com>: > On Sun, 2009-09-13 at 21:50 +0200, Pavel Stehule wrote: >> Hello >> >> > >> > ANY [TYPE] and SAME AS [TYPE OF] are syntactic sugar indeed, but they >> > are much more SQL-like than needing to write "any" or anyelement(n) as >> > argument type or return type >> > >> >> I looked on possibilities in gram.y and I thing, type identifiers >> >> "ANY TYPE" is possible without any problems (this should be synonym for "any"), >> "SAME AS" needs add "same" keyword to col_name_keywords , i.e. "same" >> is prohibited for function names - it should be a problem I afraid so this technique isn't allowed in SQL parser, or is i >> >> regards >> Pavel Stehule >> >> I found so pgparser provide some ref type syntax via % symbol. So we >> can use following syntax: >> >> CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b a%TYPE) >> RETURNS a%TYPE ... >> >> It is not pretty like SAME AS, but I am sure, so this is doable >> (parser knows it now) >> >> any other ideas? > > Hmm, maybe try to make lexer recognize "SAME AS" as one token and then > deal with other cases of 'name AS' ? I afraid so this technique isn't allowed in SQL parser, or is it? > > Or make the syntax a little uglier, > > CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a) > > CREATE OR REPLACE FUNCTION foo(ANY TYPE, TYPE OF $1) TYPE OF generate shift/reduce too :( Pavel > > > and maybe try > > CREATE OR REPLACE FUNCTION foo(a ANY TYPE, b TYPE OF a) > RETURNS ARRAY OF TYPE OF a > > instead of > > CREATE OR REPLACE FUNCTION foo(a anyelement, b anyelement) > RETURNS anyarray > >> >> regards >> Pavel Stehule > > > -- > Hannu Krosing http://www.2ndQuadrant.com > PostgreSQL Scalability and Availability > Services, Consulting and Training > > >
On Sep 14, 2009, at 12:13 AM, Pavel Stehule wrote: > 2009/9/13 decibel <decibel@decibel.org>: >> On Sep 12, 2009, at 5:54 PM, Andrew Dunstan wrote: >>> >>> decibel wrote: >>>> >>>> Speaking of concatenation... >>>> >>>> Something I find sorely missing in plpgsql is the ability to put >>>> variables inside of a string, ie: >>>> >>>> DECLARE >>>> v_table text := ... >>>> v_sql text; >>>> BEGIN >>>> v_sql := "SELECT * FROM $v_table"; >>>> >>>> Of course, I'm assuming that if it was easy to do that it would >>>> be done >>>> already... but I thought I'd just throw it out there. >>>> >>> >>> Then use a language that supports variable interpolation in >>> strings, like >>> plperl, plpythonu, plruby .... instead of plpgsql. >> >> >> Which makes executing SQL much, much harder. >> >> At least if we get sprintf dealing with strings might become a bit >> easier... > > This feature is nice - but very dangerous - it the most easy way how > do vulnerable (on SQL injection) application! How is it any worse than what people can already do? Anyone who isn't aware of the dangers of SQL injection has already screwed themselves. You're basically arguing that they would put a variable inside of quotes, but they would never use ||. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
2009/9/14 decibel <decibel@decibel.org>: > On Sep 14, 2009, at 12:13 AM, Pavel Stehule wrote: >> >> 2009/9/13 decibel <decibel@decibel.org>: >>> >>> On Sep 12, 2009, at 5:54 PM, Andrew Dunstan wrote: >>>> >>>> decibel wrote: >>>>> >>>>> Speaking of concatenation... >>>>> >>>>> Something I find sorely missing in plpgsql is the ability to put >>>>> variables inside of a string, ie: >>>>> >>>>> DECLARE >>>>> v_table text := ... >>>>> v_sql text; >>>>> BEGIN >>>>> v_sql := "SELECT * FROM $v_table"; >>>>> >>>>> Of course, I'm assuming that if it was easy to do that it would be done >>>>> already... but I thought I'd just throw it out there. >>>>> >>>> >>>> Then use a language that supports variable interpolation in strings, >>>> like >>>> plperl, plpythonu, plruby .... instead of plpgsql. >>> >>> >>> Which makes executing SQL much, much harder. >>> >>> At least if we get sprintf dealing with strings might become a bit >>> easier... >> >> This feature is nice - but very dangerous - it the most easy way how >> do vulnerable (on SQL injection) application! > > > How is it any worse than what people can already do? Anyone who isn't aware > of the dangers of SQL injection has already screwed themselves. You're > basically arguing that they would put a variable inside of quotes, but they > would never use ||. simply - people use functions quote_literal or quote_ident. regards Pavel Stehule > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > >
On Mon, Sep 14, 2009 at 1:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> How is it any worse than what people can already do? Anyone who isn't aware
>> of the dangers of SQL injection has already screwed themselves. You're
>> basically arguing that they would put a variable inside of quotes, but they
>> would never use ||.
>
> simply - people use functions quote_literal or quote_ident.
you still have use of those functions:
execute sprintf('select * from %s', quote_ident($1));
sprintf is no more or less dangerous than || operator.
merlin
2009/9/14 Merlin Moncure <mmoncure@gmail.com>:
> On Mon, Sep 14, 2009 at 1:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>> How is it any worse than what people can already do? Anyone who isn't aware
>>> of the dangers of SQL injection has already screwed themselves. You're
>>> basically arguing that they would put a variable inside of quotes, but they
>>> would never use ||.
>>
>> simply - people use functions quote_literal or quote_ident.
>
> you still have use of those functions:
> execute sprintf('select * from %s', quote_ident($1));
>
> sprintf is no more or less dangerous than || operator.
sure. I commented different feature
some := 'select * from $1'
regards
Pavel
p.s. In this case, I am not sure what is more readable:
execute 'select * from ' || quote_ident($1)
is readable well too.
>
> merlin
>
On Sep 14, 2009, at 1:02 PM, Pavel Stehule wrote:
> 2009/9/14 Merlin Moncure <mmoncure@gmail.com>:
>> On Mon, Sep 14, 2009 at 1:42 PM, Pavel Stehule
>> <pavel.stehule@gmail.com> wrote:
>>>> How is it any worse than what people can already do? Anyone who
>>>> isn't aware
>>>> of the dangers of SQL injection has already screwed themselves.
>>>> You're
>>>> basically arguing that they would put a variable inside of
>>>> quotes, but they
>>>> would never use ||.
>>>
>>> simply - people use functions quote_literal or quote_ident.
>>
>> you still have use of those functions:
>> execute sprintf('select * from %s', quote_ident($1));
>>
>> sprintf is no more or less dangerous than || operator.
>
> sure. I commented different feature
>
> some := 'select * from $1'
>
> regards
> Pavel
>
> p.s. In this case, I am not sure what is more readable:
>
> execute 'select * from ' || quote_ident($1)
>
> is readable well too.
Ahh... the problem is one of fixating on an example instead of the
overall use case.
More examples...
RETURN 'Your account is now $days_overdue days overdue. Please
contact your account manager ($manager_name) to ...';
And an example of how readability would certainly be improved...
sql := $$INSERT INTO cnu_stats.$$ || v_field_name || $$( $$ ||
v_field_name || $$ ) SELECT DISTINCT $$ || v_field_name || $$ FROM chunk t WHERE NOT EXISTS( SELECT *
FROMcnu_stats.$$ || v_field_name
|| $$ s WHERE s.$$ || v_field_name || $$ = t.$$ || v_field_name || $$ )$$
becomes
sql := $$INSERT INTO cnu_stats.${v_field_name} ( ${v_field_name} ) SELECT DISTINCT $v_field_name FROM chunk t
WHERE NOT EXISTS( SELECT * FROM cnu_stats.${v_field_name} s WHERE s.${v_field_name}
=t.$
{v_field_name} )$$
Granted, that example wouldn't be too bad with sprintf, but only
because everything is referencing the same field.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
2009/9/15 decibel <decibel@decibel.org>:
>
> On Sep 14, 2009, at 1:02 PM, Pavel Stehule wrote:
>
>> 2009/9/14 Merlin Moncure <mmoncure@gmail.com>:
>>>
>>> On Mon, Sep 14, 2009 at 1:42 PM, Pavel Stehule <pavel.stehule@gmail.com>
>>> wrote:
>>>>>
>>>>> How is it any worse than what people can already do? Anyone who isn't
>>>>> aware
>>>>> of the dangers of SQL injection has already screwed themselves. You're
>>>>> basically arguing that they would put a variable inside of quotes, but
>>>>> they
>>>>> would never use ||.
>>>>
>>>> simply - people use functions quote_literal or quote_ident.
>>>
>>> you still have use of those functions:
>>> execute sprintf('select * from %s', quote_ident($1));
>>>
>>> sprintf is no more or less dangerous than || operator.
>>
>> sure. I commented different feature
>>
>> some := 'select * from $1'
>>
>> regards
>> Pavel
>>
>> p.s. In this case, I am not sure what is more readable:
>>
>> execute 'select * from ' || quote_ident($1)
>>
>> is readable well too.
>
>
> Ahh... the problem is one of fixating on an example instead of the overall
> use case.
>
> More examples...
>
> RETURN 'Your account is now $days_overdue days overdue. Please contact your
> account manager ($manager_name) to ...';
>
> And an example of how readability would certainly be improved...
>
> sql := $$INSERT INTO cnu_stats.$$ || v_field_name || $$( $$ || v_field_name
> || $$ )
> SELECT DISTINCT $$ || v_field_name || $$
> FROM chunk t
> WHERE NOT EXISTS( SELECT * FROM cnu_stats.$$ || v_field_name || $$ s
> WHERE s.$$
> || v_field_name || $$ = t.$$ || v_field_name || $$ )$$
>
it isn't fair :) why you use $$ without single quote? And still this
case should be vulnerable on SQL injection. Maybe you or me knows,
what SQL injection means, but beginners knows nothing and this people
use following bad code:
sql := $$SELECT * FROM '${table_name}'$$} and are happy. But this code
is wrong!
> becomes
>
> sql := $$INSERT INTO cnu_stats.${v_field_name} ( ${v_field_name} )
> SELECT DISTINCT $v_field_name
> FROM chunk t
> WHERE NOT EXISTS( SELECT * FROM cnu_stats.${v_field_name} s
> WHERE s.${v_field_name} = t.${v_field_name} )$$
>
> Granted, that example wouldn't be too bad with sprintf, but only because
> everything is referencing the same field.
Really I dislike bash like syntax in SQL. What I know - SQL is
language for normal people - it is reason why it's verbose and English
like. Bash is sw for UNIX hackers. If we cut some features from others
languages, then bash, c, perl should be last (I knows these languages
well and I using it well). I thing, so there are better languages like
ADA, SQL/PSM, Python.
regards
Pavel Stehule
> --
> Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
On Tue, Sep 15, 2009 at 07:38:18AM +0200, Pavel Stehule wrote:
> it isn't fair :) why you use $$ without single quote? And still this
> case should be vulnerable on SQL injection. Maybe you or me knows,
> what SQL injection means, but beginners knows nothing and this people
> use following bad code:
>
> sql := $$SELECT * FROM '${table_name}'$$} and are happy. But this code
> is wrong!
I have an idea you will like less: have multiple interpolation codes that
automagically do the right quoting. Perhaps as extra printf like type codes.
The above then becomes:
sql := pgprintf($$SELECT * FROM %I;$$, table_name )
Where %I evaluates as if it were quote_ident(%s).
This would maybe even encourage users to do the quoting they should by
making it easy.
-dg
--
David Gould daveg@sonic.net 510 536 1443 510 282 0869
If simplicity worked, the world would be overrun with insects.