RfD: more powerful "any" types

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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)

???

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

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

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

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

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 ;)

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.

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

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>:

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

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

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

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

* 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

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

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

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

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.

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 || $$ )$$

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

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.