Thread: proposal sql: labeled function params

proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

I propose enhance current syntax that allows to specify label for any
function parameter:

fcename(expr [as label], ...)
fcename(colname, ...)

I would to allow  same behave of custom functions like xmlforest function:
postgres=# select xmlforest(a) from foo;xmlforest
-----------<a>10</a>
(1 row)

postgres=# select xmlforest(a as b) from foo;xmlforest
-----------<b>10</b>
(1 row)

Actually I am not sure what is best way for PL languages for acces to
these info. Using some system variables needed new column in pg_proc,
because collecting these needs some time and in 99% cases we don't
need it. So I prefere some system function that returns labels for
outer function call. Like

-- test
create function getlabels() returns varchar[] as $$select '{name,
age}'::varchar[]$$ language sql immutable;

create or replace function json(variadic varchar[])
returns varchar as $$
select '[' || array_to_string(    array(       select (getlabels())[i]|| ':' || $1[i]          from
generate_subscripts($1,1)g(i))  ,',') || ']'
 
$$ language sql immutable strict;

postgres=# select json('Zdenek' as name,'30' as age);        json
----------------------[name:Zdenek,age:30]
(1 row)

postgres=# select json(name, age) from person;        json
----------------------[name:Zdenek,age:30]
(1 row)

There are two possibilities a) collect labels in parse time b) collect labels in executor time

@a needs info in pg_proc, but it is simpler, @b is little bit
difficult, but doesn't need any changes in system catalog. I thinking
about b now.

Necessary changes:
=================
labels are searched in parse tree fcinfo->flinfo->fn_expr. I need
insert label into parse tree, so I it needs special node
labeled_param, For getting column reference I need to put current
exprstate to fcinfo.  Function getlabels() should take code from
ExecEvalVar function.

Any notes, ideas?

Pavel Stehule


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Thu, 2008-08-14 at 11:56 +0200, Pavel Stehule wrote:
> Hello
> 
> I propose enhance current syntax that allows to specify label for any
> function parameter:
> 
> fcename(expr [as label], ...)
> fcename(colname, ...)

also fcename(localvar, ...) if called from another function ?

How is this supposed to interact with argument names ?

> I would to allow  same behave of custom functions like xmlforest function:
> postgres=# select xmlforest(a) from foo;
>  xmlforest
> -----------
>  <a>10</a>
> (1 row)
> 
> postgres=# select xmlforest(a as b) from foo;
>  xmlforest
> -----------
>  <b>10</b>
> (1 row)

Why not just have two arguments to xmlforest(label text,value text) like
this:

"select xmlforest('b', a) from foo" 

?

> Actually I am not sure what is best way for PL languages for acces to
> these info. Using some system variables needed new column in pg_proc,
> because collecting these needs some time and in 99% cases we don't
> need it. 

Exactly, maybe it is just a bad idea in general to pass the label info
into functions using some special syntax ? 

what is wrong with passing it in regular arguments ?

I see very little gain from complicating the syntax (and function API).

maybe we will some time have keyword arguments as well and then have to
deal with syntax like

select func(arg4=7 as 'labelfor4')



> So I prefere some system function that returns labels for
> outer function call. Like
> 
> -- test
> create function getlabels() returns varchar[] as $$select '{name,
> age}'::varchar[]$$ language sql immutable;
> 
> create or replace function json(variadic varchar[])
> returns varchar as $$
> select '[' || array_to_string(
>      array(
>         select (getlabels())[i]|| ':' || $1[i]
>            from generate_subscripts($1,1) g(i))
>    ,',') || ']'
> $$ language sql immutable strict;

just write the function to take arguments as pairs (value, 'label', ...)

select json('Zdenek', 'name','30', 'age');

select json(name, 'name', age, 'age') from person;


> postgres=# select json('Zdenek' as name,'30' as age);
>          json
> ----------------------
>  [name:Zdenek,age:30]
> (1 row)
> 
> postgres=# select json(name, age) from person;
>          json
> ----------------------
>  [name:Zdenek,age:30]
> (1 row)

why special-case table fields ?

what if you wanted to rename any table fields ?

> There are two possibilities
>   a) collect labels in parse time
>   b) collect labels in executor time
> 
> @a needs info in pg_proc, but it is simpler, @b is little bit
> difficult, but doesn't need any changes in system catalog. I thinking
> about b now.
> 
> Necessary changes:
> =================
> labels are searched in parse tree fcinfo->flinfo->fn_expr. I need
> insert label into parse tree, so I it needs special node
> labeled_param, For getting column reference I need to put current
> exprstate to fcinfo.  Function getlabels() should take code from
> ExecEvalVar function.
> 
> Any notes, ideas?

To me, this whole thing feels backwards - in described cases "labels" 
seem to be just like any other data and I don't think it justifies a
special syntax.

---------------
Hannu 








Re: proposal sql: labeled function params

From
Tom Lane
Date:
Hannu Krosing <hannu@2ndQuadrant.com> writes:
> How is this supposed to interact with argument names ?

Yeah, the real problem with this proposal is that it conscripts a syntax
that we'll probably want to use in the future for argument-name-based
parameter matching.  The proposed behavior is not nearly as useful as
that would be.
        regards, tom lane


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/14 Hannu Krosing <hannu@2ndquadrant.com>:
> On Thu, 2008-08-14 at 11:56 +0200, Pavel Stehule wrote:
>> Hello
>>
>> I propose enhance current syntax that allows to specify label for any
>> function parameter:
>>
>> fcename(expr [as label], ...)
>> fcename(colname, ...)
>
> also fcename(localvar, ...) if called from another function ?
>
juju

> How is this supposed to interact with argument names ?

There is no interaction between argument names and labels. Primary
case is an using together variadic function where mostly parameters
are in one real parameter.

>
>> I would to allow  same behave of custom functions like xmlforest function:
>> postgres=# select xmlforest(a) from foo;
>>  xmlforest
>> -----------
>>  <a>10</a>
>> (1 row)
>>
>> postgres=# select xmlforest(a as b) from foo;
>>  xmlforest
>> -----------
>>  <b>10</b>
>> (1 row)
>
> Why not just have two arguments to xmlforest(label text,value text) like
> this:
>
> "select xmlforest('b', a) from foo"
>

syntax ... (a as b) is used now in SQL/XML - it's not new concept.
This concept allows shorter queries, because in some cases (where
column name is same as label name), you don't need write label. So
this is only generalisation of actually used concept.

> ?
>
>> Actually I am not sure what is best way for PL languages for acces to
>> these info. Using some system variables needed new column in pg_proc,
>> because collecting these needs some time and in 99% cases we don't
>> need it.
>
> Exactly, maybe it is just a bad idea in general to pass the label info
> into functions using some special syntax ?
>
> what is wrong with passing it in regular arguments ?
>

I thougs about $0 - but it's occupeted now.


> I see very little gain from complicating the syntax (and function API).
>
> maybe we will some time have keyword arguments as well and then have to
> deal with syntax like
>
> select func(arg4=7 as 'labelfor4')
>

it's inconsistent with column labels :(. So better select func(arg4=7
as labelfor4) or select func(arg4=column4), ... but his syntax
collidate with boolean expression. Oracle use => operator

Sal_raise(Sal_incr=>500, Emp_id=>7369) and I am for respect this syntax.

>
>
>> So I prefere some system function that returns labels for
>> outer function call. Like
>>
>> -- test
>> create function getlabels() returns varchar[] as $$select '{name,
>> age}'::varchar[]$$ language sql immutable;
>>
>> create or replace function json(variadic varchar[])
>> returns varchar as $$
>> select '[' || array_to_string(
>>      array(
>>         select (getlabels())[i]|| ':' || $1[i]
>>            from generate_subscripts($1,1) g(i))
>>    ,',') || ']'
>> $$ language sql immutable strict;
>
> just write the function to take arguments as pairs (value, 'label', ...)
>
> select json('Zdenek', 'name','30', 'age');
>
> select json(name, 'name', age, 'age') from person;

it's possible, sure. But then I have to repeat label when label is
known from column name.

>
>
>> postgres=# select json('Zdenek' as name,'30' as age);
>>          json
>> ----------------------
>>  [name:Zdenek,age:30]
>> (1 row)
>>
>> postgres=# select json(name, age) from person;
>>          json
>> ----------------------
>>  [name:Zdenek,age:30]
>> (1 row)
>
> why special-case table fields ?
>
???
or structured types .. there isn't case, you can put inside only one
scalar attribut - but it's true - it would to accept fields some like:

create type person as (name varchar, age integer);

select json(a.name, a.age, b.name, b.age) from ...
and generate [a.name: ...., a.age: ....

> what if you wanted to rename any table fields ?
>

it's not renaming, It's function metadata (and default is generated
from column names).

>> There are two possibilities
>>   a) collect labels in parse time
>>   b) collect labels in executor time
>>
>> @a needs info in pg_proc, but it is simpler, @b is little bit
>> difficult, but doesn't need any changes in system catalog. I thinking
>> about b now.
>>
>> Necessary changes:
>> =================
>> labels are searched in parse tree fcinfo->flinfo->fn_expr. I need
>> insert label into parse tree, so I it needs special node
>> labeled_param, For getting column reference I need to put current
>> exprstate to fcinfo.  Function getlabels() should take code from
>> ExecEvalVar function.
>>
>> Any notes, ideas?
>
> To me, this whole thing feels backwards - in described cases "labels"
> seem to be just like any other data and I don't think it justifies a
> special syntax.
>
> ---------------
> Hannu
>
>
>
>
>
>
>


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/15 Tom Lane <tgl@sss.pgh.pa.us>:
> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> How is this supposed to interact with argument names ?
>
> Yeah, the real problem with this proposal is that it conscripts a syntax
> that we'll probably want to use in the future for argument-name-based
> parameter matching.  The proposed behavior is not nearly as useful as
> that would be.

It isn't. As Hannu showed these features should live in harmony (if we
will accept Oracle's syntax). I see as real problem new column in
pg_proc, that allow quickly chose between labeled and non labeled
functions - and then collect labels in parse time. This is way for
adding parameter info into variadic function - it's main goal of this
proposal. Without column in pg_proc it could same slowdowns like first
variadic function's implementation.

Regards
Pavel Stehule
>
>                        regards, tom lane
>


Re: proposal sql: labeled function params

From
Peter Eisentraut
Date:
Am Thursday, 14. August 2008 schrieb Pavel Stehule:
> I propose enhance current syntax that allows to specify label for any
> function parameter:
>
> fcename(expr [as label], ...)
> fcename(colname, ...)
>
> I would to allow  same behave of custom functions like xmlforest function:
> postgres=# select xmlforest(a) from foo;
>  xmlforest
> -----------
>  <a>10</a>
> (1 row)

Do you have a use case for this outside of XML?


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/15 Peter Eisentraut <peter_e@gmx.net>:
> Am Thursday, 14. August 2008 schrieb Pavel Stehule:
>> I propose enhance current syntax that allows to specify label for any
>> function parameter:
>>
>> fcename(expr [as label], ...)
>> fcename(colname, ...)
>>
>> I would to allow  same behave of custom functions like xmlforest function:
>> postgres=# select xmlforest(a) from foo;
>>  xmlforest
>> -----------
>>  <a>10</a>
>> (1 row)
>
> Do you have a use case for this outside of XML?
>

JSON and similar (custom) protocols

Pavel


Re: proposal sql: labeled function params

From
Peter Eisentraut
Date:
Am Friday, 15. August 2008 schrieb Tom Lane:
> Hannu Krosing <hannu@2ndQuadrant.com> writes:
> > How is this supposed to interact with argument names ?
>
> Yeah, the real problem with this proposal is that it conscripts a syntax
> that we'll probably want to use in the future for argument-name-based
> parameter matching.  The proposed behavior is not nearly as useful as
> that would be.

I am not at all convinced about the proposed feature, but is that really a 
syntax we would use for function calls with named parameters?

Random googling shows me that Oracle appears to use a syntax like
   name => value

This is actually a feature that I would like to see implemented soonish, so if 
anyone has input on the possible syntax consequences, please comment.


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/15 Peter Eisentraut <peter_e@gmx.net>:
> Am Friday, 15. August 2008 schrieb Tom Lane:
>> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> > How is this supposed to interact with argument names ?
>>
>> Yeah, the real problem with this proposal is that it conscripts a syntax
>> that we'll probably want to use in the future for argument-name-based
>> parameter matching.  The proposed behavior is not nearly as useful as
>> that would be.
>
> I am not at all convinced about the proposed feature, but is that really a
> syntax we would use for function calls with named parameters?
>
> Random googling shows me that Oracle appears to use a syntax like
>
>    name => value
>

I vote this syntax too. So this second feature - named params X labels
for params. Labels for params is related to my work on variadic
functions. Named params needs default param's values - and some more
of changes in parser. Somebody have to solve conflict between params
and expression.

Pavel




> This is actually a feature that I would like to see implemented soonish, so if
> anyone has input on the possible syntax consequences, please comment.
>


Re: proposal sql: labeled function params

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Random googling shows me that Oracle appears to use a syntax like
>     name => value
> This is actually a feature that I would like to see implemented soonish, so if 
> anyone has input on the possible syntax consequences, please comment.

We've been over this territory before.  The problem with "name => value"
is that it requires reserving a perfectly good user-defined operator name.
"value AS name", on the other hand, accomplishes the same in a more
SQL-looking fashion with no new reserved word (since AS is already
fully reserved).
        regards, tom lane


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Fri, 2008-08-15 at 10:01 -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Random googling shows me that Oracle appears to use a syntax like
> >     name => value
> > This is actually a feature that I would like to see implemented soonish, so if 
> > anyone has input on the possible syntax consequences, please comment.
> 
> We've been over this territory before.  The problem with "name => value"
> is that it requires reserving a perfectly good user-defined operator name.

We could declare, that using operator => in function argument expression
requires parenthesis : func( a => (b => c) means param a with value
expression (b => c) nad just func((b => c)) means first param with value
(b=>c)

the main use of named params is calling functions with default values,
and giving some params. there I'm more concerned about default args and
rules for finding right function in presence of functions with both
multiple args and default values for some.

create function f(a int) ...

create function f(a int, b int default 7) 

create function f(text text)

and then calling f(4) - which one would it call

what about f('4')

Of course, we could also have default values without named params, and
just require keyword DEFAULT where we want default value :)

> "value AS name", on the other hand, accomplishes the same in a more
> SQL-looking fashion with no new reserved word (since AS is already
> fully reserved).

would it be more natural / SQL-like to use "value AS name" or "name AS
value" ?

-------------
Hannu




Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Fri, 2008-08-15 at 14:54 +0200, Pavel Stehule wrote:
> 2008/8/15 Peter Eisentraut <peter_e@gmx.net>:
> > Am Thursday, 14. August 2008 schrieb Pavel Stehule:
> >> I propose enhance current syntax that allows to specify label for any
> >> function parameter:
> >>
> >> fcename(expr [as label], ...)
> >> fcename(colname, ...)
> >>
> >> I would to allow  same behave of custom functions like xmlforest function:
> >> postgres=# select xmlforest(a) from foo;
> >>  xmlforest
> >> -----------
> >>  <a>10</a>
> >> (1 row)
> >
> > Do you have a use case for this outside of XML?
> >
> 
> JSON and similar (custom) protocols

why not use a format string, or any other separate (sub)language ?

select json('[name:$1, age: $2]', name, age) from students;

------------
Hannu



Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/15 Hannu Krosing <hannu@2ndquadrant.com>:
> On Fri, 2008-08-15 at 14:54 +0200, Pavel Stehule wrote:
>> 2008/8/15 Peter Eisentraut <peter_e@gmx.net>:
>> > Am Thursday, 14. August 2008 schrieb Pavel Stehule:
>> >> I propose enhance current syntax that allows to specify label for any
>> >> function parameter:
>> >>
>> >> fcename(expr [as label], ...)
>> >> fcename(colname, ...)
>> >>
>> >> I would to allow  same behave of custom functions like xmlforest function:
>> >> postgres=# select xmlforest(a) from foo;
>> >>  xmlforest
>> >> -----------
>> >>  <a>10</a>
>> >> (1 row)
>> >
>> > Do you have a use case for this outside of XML?
>> >
>>
>> JSON and similar (custom) protocols
>
> why not use a format string, or any other separate (sub)language ?
>
> select json('[name:$1, age: $2]', name, age) from students;
>

because you have to write labels, where labels are equal with column
names. I would to add same comfort like SQL/XML functions.

Pavel


> ------------
> Hannu
>
>


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/15 Hannu Krosing <hannu@2ndquadrant.com>:
> On Fri, 2008-08-15 at 10:01 -0400, Tom Lane wrote:
>> Peter Eisentraut <peter_e@gmx.net> writes:
>> > Random googling shows me that Oracle appears to use a syntax like
>> >     name => value
>> > This is actually a feature that I would like to see implemented soonish, so if
>> > anyone has input on the possible syntax consequences, please comment.
>>
>> We've been over this territory before.  The problem with "name => value"
>> is that it requires reserving a perfectly good user-defined operator name.
>
> We could declare, that using operator => in function argument expression
> requires parenthesis : func( a => (b => c) means param a with value
> expression (b => c) nad just func((b => c)) means first param with value
> (b=>c)
>

or just use := operator?

select new_person(name := 'Smith') - it's simple and consistent with pl/pgsql.

> the main use of named params is calling functions with default values,
> and giving some params. there I'm more concerned about default args and
> rules for finding right function in presence of functions with both
> multiple args and default values for some.
>
> create function f(a int) ...
>
> create function f(a int, b int default 7)
>
> create function f(text text)
>
> and then calling f(4) - which one would it call
>
> what about f('4')
>
> Of course, we could also have default values without named params, and
> just require keyword DEFAULT where we want default value :)
>
>> "value AS name", on the other hand, accomplishes the same in a more
>> SQL-looking fashion with no new reserved word (since AS is already
>> fully reserved).
>
> would it be more natural / SQL-like to use "value AS name" or "name AS
> value" ?
>

it's question, because SQL wit AS clause don't specify value, it
specifies label.

Regards
Pavel


> -------------
> Hannu
>
>
>


Re: proposal sql: labeled function params

From
Tom Lane
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> or just use := operator?

You're still commandeering an operator name that wasn't reserved before.
This one doesn't even have the feeble excuse of being Oracle-compatible.
        regards, tom lane


Re: proposal sql: labeled function params

From
Peter Eisentraut
Date:
On Saturday 16 August 2008 09:38:41 Pavel Stehule wrote:
> because you have to write labels, where labels are equal with column
> names. I would to add same comfort like SQL/XML functions.

Just a thought: You might be able to design this in some way to work on top of 
named parameter calling.  Define a function with variadic arguments and allow 
passing arbitrary parameter names.  Before you can use that to implement 
xmlforest in user space you need to work out the issue of passing arbitrary 
argument types.  But that is also something that would be interesting for 
other purposes.


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sat, 2008-08-16 at 08:44 +0200, Pavel Stehule wrote:
> Hello

> >> "value AS name", on the other hand, accomplishes the same in a more
> >> SQL-looking fashion with no new reserved word (since AS is already
> >> fully reserved).
> >
> > would it be more natural / SQL-like to use "value AS name" or "name AS
> > value" ?
> >
> 
> it's question, because SQL wit AS clause don't specify value, it
> specifies label.

A "label" is the same thing as "variable"/"attribute"/"argument name" in
all  programming languages I can think of. Why do you need two kinds of
argument names in postgreSQL ?

maybe you are after something like keyword arguments in python ?

http://docs.python.org/tut/node6.html#SECTION006720000000000000000

keyword arguments are a way of saying that you don't know all variable
names (or "labels" if you prefer) at function defining time and are
going to pass them in when calling. 

It's kind of extended variadic argument, only with names and types for
each extra arg. 

Of course we could extend this to have shortcut of passing in original
variable or field names automatically, without you having to explicitly
write it down that is fun(name) instead of fun(name=name) but I'm not
sure it is actually a good idea. SQL in general has not been very terse
language. 

But I sure would like to have the flexibility of keyword arguments in
PostgreSQL .


> Regards
> Pavel
> 
> 
> > -------------
> > Hannu
> >
> >
> >



Re: proposal sql: labeled function params

From
Decibel!
Date:
On Aug 15, 2008, at 1:20 PM, Hannu Krosing wrote:
>> "value AS name", on the other hand, accomplishes the same in a more
>> SQL-looking fashion with no new reserved word (since AS is already
>> fully reserved).
>
> would it be more natural / SQL-like to use "value AS name" or "name AS
> value" ?


IMHO, *natural* would be name *something* value, because that's how  
every other language I've seen does it.

SQL-like would be value AS name, but I'm not a fan of putting the  
value before the name. And I think value AS name will just lead to a  
ton of confusion.

Since I think it'd be very unusual to do a => (b => c), I'd vote that  
we just go with =>. Anyone trying to do a => b => c should  
immediately question if that would work.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/16 Decibel! <decibel@decibel.org>:
> On Aug 15, 2008, at 1:20 PM, Hannu Krosing wrote:
>>>
>>> "value AS name", on the other hand, accomplishes the same in a more
>>> SQL-looking fashion with no new reserved word (since AS is already
>>> fully reserved).
>>
>> would it be more natural / SQL-like to use "value AS name" or "name AS
>> value" ?
>
>
> IMHO, *natural* would be name *something* value, because that's how every
> other language I've seen does it.
>
> SQL-like would be value AS name, but I'm not a fan of putting the value
> before the name. And I think value AS name will just lead to a ton of
> confusion.
>
> Since I think it'd be very unusual to do a => (b => c), I'd vote that we
> just go with =>. Anyone trying to do a => b => c should immediately question
> if that would work.

I'll look on this syntax - what is really means for implementation. I
thing, mostly of us prefer this or similar syntax.

Regards
Pavel Stehule

> --
> Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sun, 2008-08-17 at 08:06 +0200, Pavel Stehule wrote:
> 2008/8/16 Decibel! <decibel@decibel.org>:
> > On Aug 15, 2008, at 1:20 PM, Hannu Krosing wrote:
> >>>
> >>> "value AS name", on the other hand, accomplishes the same in a more
> >>> SQL-looking fashion with no new reserved word (since AS is already
> >>> fully reserved).
> >>
> >> would it be more natural / SQL-like to use "value AS name" or "name AS
> >> value" ?
> >
> >
> > IMHO, *natural* would be name *something* value, because that's how every
> > other language I've seen does it.
> >
> > SQL-like would be value AS name, but I'm not a fan of putting the value
> > before the name. And I think value AS name will just lead to a ton of
> > confusion.
> >
> > Since I think it'd be very unusual to do a => (b => c), I'd vote that we
> > just go with =>. Anyone trying to do a => b => c should immediately question
> > if that would work.
> 
> I'll look on this syntax - what is really means for implementation. I
> thing, mostly of us prefer this or similar syntax.

Actually the most "natural" syntax to me is just f(name=value) similar
to how UPDATE does it. It has the added benefit of _not_ forcing us to
make a operator reserved (AFAIK "=" can't be used to define new ops)

And I still don't think we need two kinds of names ("argument name" and
"label"). I'd rather see us have the syntax for this be similar to
python's keyword arguments, even though I'm not entirely opposed to
automatically generating the name= part if it comes from existing name
(variable, function argument or column name).

---------------
Hannu




Re: proposal sql: labeled function params

From
Tom Lane
Date:
Hannu Krosing <hannu@2ndQuadrant.com> writes:
> Actually the most "natural" syntax to me is just f(name=value) similar
> to how UPDATE does it. It has the added benefit of _not_ forcing us to
> make a operator reserved (AFAIK "=" can't be used to define new ops)

*What* are you thinking?
        regards, tom lane


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> On Sun, 2008-08-17 at 08:06 +0200, Pavel Stehule wrote:
>> 2008/8/16 Decibel! <decibel@decibel.org>:
>> > On Aug 15, 2008, at 1:20 PM, Hannu Krosing wrote:
>> >>>
>> >>> "value AS name", on the other hand, accomplishes the same in a more
>> >>> SQL-looking fashion with no new reserved word (since AS is already
>> >>> fully reserved).
>> >>
>> >> would it be more natural / SQL-like to use "value AS name" or "name AS
>> >> value" ?
>> >
>> >
>> > IMHO, *natural* would be name *something* value, because that's how every
>> > other language I've seen does it.
>> >
>> > SQL-like would be value AS name, but I'm not a fan of putting the value
>> > before the name. And I think value AS name will just lead to a ton of
>> > confusion.
>> >
>> > Since I think it'd be very unusual to do a => (b => c), I'd vote that we
>> > just go with =>. Anyone trying to do a => b => c should immediately question
>> > if that would work.
>>
>> I'll look on this syntax - what is really means for implementation. I
>> thing, mostly of us prefer this or similar syntax.
>
> Actually the most "natural" syntax to me is just f(name=value) similar
> to how UPDATE does it. It has the added benefit of _not_ forcing us to
> make a operator reserved (AFAIK "=" can't be used to define new ops)
>
> And I still don't think we need two kinds of names ("argument name" and
> "label"). I'd rather see us have the syntax for this be similar to
> python's keyword arguments, even though I'm not entirely opposed to
> automatically generating the name= part if it comes from existing name
> (variable, function argument or column name).
>

I wouldn't mix together two features - argument name (keyword
argument) and labels. Its two different features.

Regards
Pavel Stehule

> ---------------
> Hannu
>
>
>


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@2ndQuadrant.com> writes:
> > Actually the most "natural" syntax to me is just f(name=value) similar
> > to how UPDATE does it. It has the added benefit of _not_ forcing us to
> > make a operator reserved (AFAIK "=" can't be used to define new ops)
> 
> *What* are you thinking?

I think that we could achieve what Pavel was after by allowing one to
define something similar to keyword arguments in python.

maybe allow input RECORD type, which is instantiated at call time by
giving extra arguments to function call:

CREATE FUNCTION f_kw(r record) ....

and then if you call it like this:

SELECT ... f_kw(name='bob', age=7::int) 

then function gets as its input a record 
which can be accessed in pl/pgsql like 

r.name r.age

and if terseness is really appreciated then the it could also be called
like this

SELECT ... f_kw(name, age) from people where name='bob';

which is rewritten to 

SELECT ... f_kw(name=name, age=age) from people where name='bob';


not sure if we should allow defining SETOF RECORD and then enable
calling it with 

SELECT *  FROM f_kw(   VALUES(name='bob', age=7::int),    VALUES(name='bill', age=42::int );

or somesuch

------------------
Hannu




Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hannu

it's not possible in plpgsql, because we are not able iterate via record.

Pavel

2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
>> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> > Actually the most "natural" syntax to me is just f(name=value) similar
>> > to how UPDATE does it. It has the added benefit of _not_ forcing us to
>> > make a operator reserved (AFAIK "=" can't be used to define new ops)
>>
>> *What* are you thinking?
>
> I think that we could achieve what Pavel was after by allowing one to
> define something similar to keyword arguments in python.
>
> maybe allow input RECORD type, which is instantiated at call time by
> giving extra arguments to function call:
>
> CREATE FUNCTION f_kw(r record) ....
>
> and then if you call it like this:
>
> SELECT ... f_kw(name='bob', age=7::int)
>
> then function gets as its input a record
> which can be accessed in pl/pgsql like
>
> r.name r.age
>
> and if terseness is really appreciated then the it could also be called
> like this
>
> SELECT ... f_kw(name, age) from people where name='bob';
>
> which is rewritten to
>
> SELECT ... f_kw(name=name, age=age) from people where name='bob';
>
>
> not sure if we should allow defining SETOF RECORD and then enable
> calling it with
>
> SELECT *
>  FROM f_kw(
>    VALUES(name='bob', age=7::int),
>    VALUES(name='bill', age=42::int
>  );
>
> or somesuch
>
> ------------------
> Hannu
>
>
>


Re: proposal sql: labeled function params

From
"Asko Oja"
Date:
Not able to means not implementable o not implemented ?

On Sun, Aug 17, 2008 at 6:59 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hannu

it's not possible inNot able to  plpgsql, because we are not able iterate via record.

Pavel

2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
>> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> > Actually the most "natural" syntax to me is just f(name=value) similar
>> > to how UPDATE does it. It has the added benefit of _not_ forcing us to
>> > make a operator reserved (AFAIK "=" can't be used to define new ops)
>>
>> *What* are you thinking?
>
> I think that we could achieve what Pavel was after by allowing one to
> define something similar to keyword arguments in python.
>
> maybe allow input RECORD type, which is instantiated at call time by
> giving extra arguments to function call:
>
> CREATE FUNCTION f_kw(r record) ....
>
> and then if you call it like this:
>
> SELECT ... f_kw(name='bob', age=7::int)
>
> then function gets as its input a record
> which can be accessed in pl/pgsql like
>
> r.name r.age
>
> and if terseness is really appreciated then the it could also be called
> like this
>
> SELECT ... f_kw(name, age) from people where name='bob';
>
> which is rewritten to
>
> SELECT ... f_kw(name=name, age=age) from people where name='bob';
>
>
> not sure if we should allow defining SETOF RECORD and then enable
> calling it with
>
> SELECT *
>  FROM f_kw(
>    VALUES(name='bob', age=7::int),
>    VALUES(name='bill', age=42::int
>  );
>
> or somesuch
>
> ------------------
> Hannu
>
>
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: proposal sql: labeled function params

From
Gregory Stark
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:

> 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
>> On Sun, 2008-08-17 at 08:06 +0200, Pavel Stehule wrote:
>>> 2008/8/16 Decibel! <decibel@decibel.org>:
>>> > SQL-like would be value AS name, but I'm not a fan of putting the value
>>> > before the name. And I think value AS name will just lead to a ton of
>>> > confusion.
>>> >
>>> > Since I think it'd be very unusual to do a => (b => c), I'd vote that we
>>> > just go with =>. Anyone trying to do a => b => c should immediately question
>>> > if that would work.
>>>
>>> I'll look on this syntax - what is really means for implementation. I
>>> thing, mostly of us prefer this or similar syntax.
>>
>> Actually the most "natural" syntax to me is just f(name=value) similar
>> to how UPDATE does it. It has the added benefit of _not_ forcing us to
>> make a operator reserved (AFAIK "=" can't be used to define new ops)

This whole thing seems like a ridiculous idea. It's a fancy way of passing an
extra parameter to the function intended to be used for a particular "label"
purpose. Your xml function could just as easily take two functions
f(name,value) instead of using a special spelling for ",".

That it is easily confused with named parameters means there are huge
downsides and no significant up-sides to having this trivial little bit of
syntactic sugar.

To say nothing that using "=>" or anything like that would be just completely
un-SQLish.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!
 


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/17 Asko Oja <ascoja@gmail.com>:
> Not able to means not implementable o not implemented ?

Almost not implementable - plpgsql is too static language.

>
> On Sun, Aug 17, 2008 at 6:59 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> Hannu
>>
>> it's not possible inNot able to  plpgsql, because we are not able iterate
>> via record.
>>
>> Pavel
>>
>> 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
>> > On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
>> >> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> >> > Actually the most "natural" syntax to me is just f(name=value)
>> >> > similar
>> >> > to how UPDATE does it. It has the added benefit of _not_ forcing us
>> >> > to
>> >> > make a operator reserved (AFAIK "=" can't be used to define new ops)
>> >>
>> >> *What* are you thinking?
>> >
>> > I think that we could achieve what Pavel was after by allowing one to
>> > define something similar to keyword arguments in python.
>> >
>> > maybe allow input RECORD type, which is instantiated at call time by
>> > giving extra arguments to function call:
>> >
>> > CREATE FUNCTION f_kw(r record) ....
>> >
>> > and then if you call it like this:
>> >
>> > SELECT ... f_kw(name='bob', age=7::int)
>> >
>> > then function gets as its input a record
>> > which can be accessed in pl/pgsql like
>> >
>> > r.name r.age
>> >
>> > and if terseness is really appreciated then the it could also be called
>> > like this
>> >
>> > SELECT ... f_kw(name, age) from people where name='bob';
>> >
>> > which is rewritten to
>> >
>> > SELECT ... f_kw(name=name, age=age) from people where name='bob';
>> >
>> >
>> > not sure if we should allow defining SETOF RECORD and then enable
>> > calling it with
>> >
>> > SELECT *
>> >  FROM f_kw(
>> >    VALUES(name='bob', age=7::int),
>> >    VALUES(name='bill', age=42::int
>> >  );
>> >
>> > or somesuch
>> >
>> > ------------------
>> > Hannu
>> >
>> >
>> >
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
>


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote:
> Hannu
> 
> it's not possible in plpgsql, because we are not able iterate via record.

just add function for iterating over record :)

create or replace function json(r record)
returns varchar as $$
select '[' || array_to_string(    array(       select (getfieldnames(r))[i]|| ':' || getfieldvalue(r,i)          from
generate_subscripts(r,1)g(i))  ,',') || ']'
 
$$ language sql immutable strict;

(this is a straight rewrite of your original sample, one can also do it
in a simpler way, with a function returning SETOF (name, value) pairs)

postgres=# select json(name='Zdenek',age=30);        json
----------------------[name:Zdenek,age:30]
(1 row)

postgres=# select json(name, age) from person;        json
----------------------[name:Zdenek,age:30]
(1 row)

BTW, json actually requires quoting names/labels, so the answer should
be

["name":"Zdenek","age":"30"]


> 
> 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> > On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
> >> Hannu Krosing <hannu@2ndQuadrant.com> writes:
> >> > Actually the most "natural" syntax to me is just f(name=value) similar
> >> > to how UPDATE does it. It has the added benefit of _not_ forcing us to
> >> > make a operator reserved (AFAIK "=" can't be used to define new ops)
> >>
> >> *What* are you thinking?
> >
> > I think that we could achieve what Pavel was after by allowing one to
> > define something similar to keyword arguments in python.
> >
> > maybe allow input RECORD type, which is instantiated at call time by
> > giving extra arguments to function call:
> >
> > CREATE FUNCTION f_kw(r record) ....
> >
> > and then if you call it like this:
> >
> > SELECT ... f_kw(name='bob', age=7::int)
> >
> > then function gets as its input a record
> > which can be accessed in pl/pgsql like
> >
> > r.name r.age
> >
> > and if terseness is really appreciated then the it could also be called
> > like this
> >
> > SELECT ... f_kw(name, age) from people where name='bob';
> >
> > which is rewritten to
> >
> > SELECT ... f_kw(name=name, age=age) from people where name='bob';
> >
> >
> > not sure if we should allow defining SETOF RECORD and then enable
> > calling it with
> >
> > SELECT *
> >  FROM f_kw(
> >    VALUES(name='bob', age=7::int),
> >    VALUES(name='bill', age=42::int
> >  );
> >
> > or somesuch
> >
> > ------------------
> > Hannu
> >
> >
> >
> 



Re: proposal sql: labeled function params

From
"Robert Haas"
Date:
> Actually the most "natural" syntax to me is just f(name=value) similar
> to how UPDATE does it. It has the added benefit of _not_ forcing us to
> make a operator reserved (AFAIK "=" can't be used to define new ops)

The problem with this is that

SELECT foo(a = b)

...is already valid syntax.  It means compare a with b and pass the
resulting boolean to foo.  I'm almost positive that changing this
would break all kinds of existing code (and probably create a lot of
grammar problems too).  It's not an issue with SET because in that
case the "name=" part of the syntax is required rather than optional.

Any other operator you pick is going to have this same problem unless
it's already impossible to use that operator as part of an expression.For that reason, while I'm not convinced of the
valueof the feature,
 
if we're going to support it then ISTM that expr AS label is the way
to go.  That also has the advantage of being consistent with the
syntax for table and column aliasing.

...Robert


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sun, 2008-08-17 at 18:24 -0400, Robert Haas wrote:
> > Actually the most "natural" syntax to me is just f(name=value) similar
> > to how UPDATE does it. It has the added benefit of _not_ forcing us to
> > make a operator reserved (AFAIK "=" can't be used to define new ops)
> 
> The problem with this is that
> 
> SELECT foo(a = b)
>
> ...is already valid syntax.  

uups, completely forgot dual use of = for both assignment and
comparison.

> It means compare a with b and pass the
> resulting boolean to foo.  I'm almost positive that changing this
> would break all kinds of existing code (and probably create a lot of
> grammar problems too).  It's not an issue with SET because in that
> case the "name=" part of the syntax is required rather than optional.

Maybe we can do without any "keyword arguments" or "labeled function
params" if we define a way to construct records in-place.

something like

RECORD( 'Zdanek'::text AS name, 22::int AS age); -- like SELECT

or 

RECORD( name 'Zdanek'::text, age 22::int); -- like CREATE TABLE/TYPE


or 

RECORD(name, age) .... from sometable; -- get values & types from table

?

Then we could pass these records to any PL for processing with minimal
confusion to programmer, and without introducing new concepts like
"variadic argument position labels"

-------------
Hannu




Re: proposal sql: labeled function params

From
"Robert Haas"
Date:
> uups, completely forgot dual use of = for both assignment and
> comparison.
>
> Maybe we can do without any "keyword arguments" or "labeled function
> params" if we define a way to construct records in-place.

That sounds a lot cleaner to me.

> something like
> RECORD( 'Zdanek'::text AS name, 22::int AS age); -- like SELECT
> or
> RECORD( name 'Zdanek'::text, age 22::int); -- like CREATE TABLE/TYPE
> or
> RECORD(name, age) .... from sometable; -- get values & types from table

In most cases, you can just do this using SELECT without the need for
any special syntax.  For example:

SELECT json(p) FROM person p;
SELECT json(p) FROM (SELECT first_name, last_name FROM person) p;

The only problem is that this doesn't work if you try to put the
select into the attribute list:

SELECT json(select first_name, last_name) FROM person p;
ERROR: syntax error at or near "select"
SELECT json((select first_name, last_name)) FROM person p;
ERROR: subquery must return only one column

Unfortunately this is a pretty common situation, because you might
easily want to do:

SELECT json((select first_name, last_name)), age FROM person p;

...and you are out of luck.

I'm not sure whether the ROW() syntax could possibly be extended to
address this problem.  It doesn't seem to help in its present form.

> Then we could pass these records to any PL for processing with minimal
> confusion to programmer, and without introducing new concepts like
> "variadic argument position labels"

Amen.

...Robert


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote:
>> Hannu
>>
>> it's not possible in plpgsql, because we are not able iterate via record.
>
> just add function for iterating over record :)

it's not easy, when iterating should be fast - when record's field has
different types, than isn't possible cache execution plan.

Pavel

>
> create or replace function json(r record)
> returns varchar as $$
> select '[' || array_to_string(
>     array(
>        select (getfieldnames(r))[i]|| ':' || getfieldvalue(r,i)
>           from generate_subscripts(r,1) g(i))
>   ,',') || ']'
> $$ language sql immutable strict;
>
> (this is a straight rewrite of your original sample, one can also do it
> in a simpler way, with a function returning SETOF (name, value) pairs)
>
> postgres=# select json(name='Zdenek',age=30);
>         json
> ----------------------
>  [name:Zdenek,age:30]
> (1 row)
>
> postgres=# select json(name, age) from person;
>         json
> ----------------------
>  [name:Zdenek,age:30]
> (1 row)
>
> BTW, json actually requires quoting names/labels, so the answer should
> be
>
> ["name":"Zdenek","age":"30"]
>
>
>>
>> 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
>> > On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
>> >> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> >> > Actually the most "natural" syntax to me is just f(name=value) similar
>> >> > to how UPDATE does it. It has the added benefit of _not_ forcing us to
>> >> > make a operator reserved (AFAIK "=" can't be used to define new ops)
>> >>
>> >> *What* are you thinking?
>> >
>> > I think that we could achieve what Pavel was after by allowing one to
>> > define something similar to keyword arguments in python.
>> >
>> > maybe allow input RECORD type, which is instantiated at call time by
>> > giving extra arguments to function call:
>> >
>> > CREATE FUNCTION f_kw(r record) ....
>> >
>> > and then if you call it like this:
>> >
>> > SELECT ... f_kw(name='bob', age=7::int)
>> >
>> > then function gets as its input a record
>> > which can be accessed in pl/pgsql like
>> >
>> > r.name r.age
>> >
>> > and if terseness is really appreciated then the it could also be called
>> > like this
>> >
>> > SELECT ... f_kw(name, age) from people where name='bob';
>> >
>> > which is rewritten to
>> >
>> > SELECT ... f_kw(name=name, age=age) from people where name='bob';
>> >
>> >
>> > not sure if we should allow defining SETOF RECORD and then enable
>> > calling it with
>> >
>> > SELECT *
>> >  FROM f_kw(
>> >    VALUES(name='bob', age=7::int),
>> >    VALUES(name='bill', age=42::int
>> >  );
>> >
>> > or somesuch
>> >
>> > ------------------
>> > Hannu
>> >
>> >
>> >
>>
>
>


Re: proposal sql: labeled function params

From
Peter Eisentraut
Date:
Am Saturday, 16. August 2008 schrieb Hannu Krosing:
> A "label" is the same thing as "variable"/"attribute"/"argument name" in
> all  programming languages I can think of. Why do you need two kinds of
> argument names in postgreSQL ?
>
> maybe you are after something like keyword arguments in python ?
>
> http://docs.python.org/tut/node6.html#SECTION006720000000000000000
>
> keyword arguments are a way of saying that you don't know all variable
> names (or "labels" if you prefer) at function defining time and are
> going to pass them in when calling.

I think we are beginning to talk about the same thing.  (Meaning you and me -- 
not sure about the rest. :) )


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Mon, 2008-08-18 at 08:53 +0200, Pavel Stehule wrote:
> 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> > On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote:
> >> Hannu
> >>
> >> it's not possible in plpgsql, because we are not able iterate via record.
> >
> > just add function for iterating over record :)
> 
> it's not easy, when iterating should be fast - when record's field has
> different types, than isn't possible cache execution plan.

the iterator should convert them to some common type like TEXT

------------
Hannu




Re: [SPAM?]: Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Mon, 2008-08-18 at 10:51 +0300, Peter Eisentraut wrote:
> Am Saturday, 16. August 2008 schrieb Hannu Krosing:
> > A "label" is the same thing as "variable"/"attribute"/"argument name" in
> > all  programming languages I can think of. Why do you need two kinds of
> > argument names in postgreSQL ?
> >
> > maybe you are after something like keyword arguments in python ?
> >
> > http://docs.python.org/tut/node6.html#SECTION006720000000000000000
> >
> > keyword arguments are a way of saying that you don't know all variable
> > names (or "labels" if you prefer) at function defining time and are
> > going to pass them in when calling.
> 
> I think we are beginning to talk about the same thing.  (Meaning you and me -- 
> not sure about the rest. :) )

Yes, I noticed that. Maybe we are onto something ;)

The exact moment I sent my mail away, I received yours.

------------
Hannu



Re: proposal sql: labeled function params

From
Bruce Momjian
Date:
Is this a TODO?

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Mon, 2008-08-18 at 08:53 +0200, Pavel Stehule wrote:
> > 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> > > On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote:
> > >> Hannu
> > >>
> > >> it's not possible in plpgsql, because we are not able iterate via record.
> > >
> > > just add function for iterating over record :)
> > 
> > it's not easy, when iterating should be fast - when record's field has
> > different types, than isn't possible cache execution plan.
> 
> the iterator should convert them to some common type like TEXT
> 
> ------------
> Hannu
> 
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Mon, 2008-08-18 at 11:19 -0400, Bruce Momjian wrote:
> Is this a TODO?

I don't think we have a TODO yet.

Maybe a TBD :)

> ---------------------------------------------------------------------------
> 
> Hannu Krosing wrote:
> > On Mon, 2008-08-18 at 08:53 +0200, Pavel Stehule wrote:
> > > 2008/8/17 Hannu Krosing <hannu@2ndquadrant.com>:
> > > > On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote:
> > > >> Hannu
> > > >>
> > > >> it's not possible in plpgsql, because we are not able iterate via record.
> > > >
> > > > just add function for iterating over record :)
> > > 
> > > it's not easy, when iterating should be fast - when record's field has
> > > different types, than isn't possible cache execution plan.
> > 
> > the iterator should convert them to some common type like TEXT
> > 
> > ------------
> > Hannu
> > 
> > 
> > 
> > -- 
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> 



Re: proposal sql: labeled function params

From
"Robert Haas"
Date:
There may be a TODO in this thread somewhere, but I think this
particular suggestion has drifted pretty far from the problem that
Pavel was trying to solve.

...Robert

On Mon, Aug 18, 2008 at 11:19 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> Is this a TODO?
>
>> > >> it's not possible in plpgsql, because we are not able iterate via record.
>> > >
>> > > just add function for iterating over record :)
>> >
>> > it's not easy, when iterating should be fast - when record's field has
>> > different types, than isn't possible cache execution plan.
>>
>> the iterator should convert them to some common type like TEXT


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

I understand now why Oracle use => symbol for named params. This isn't
used so operator - so implementation is trivial.
postgres=# create function x(a boolean) returns bool as $$select $1$$
language sql;
CREATE FUNCTION
Time: 5,549 ms
postgres=# select x(a => true);x
---t
(1 row)

Time: 0,566 ms
postgres=# select x(a => 0 >= 1);x
---f
(1 row)

Time: 0,772 ms
postgres=# select x(a => 0 <= 1);x
---t
(1 row)

Time: 0,633 ms
postgres=# select x(a => 0 <= 1);

it could live together with labels
postgres=# select x(a => 0 <= 1 as boo);x
---t
(1 row)

there are not any conflict. nice (operator => is never used).

I dislike to use AS for named params - it has some unhappy consequences:
a) it merge two features together (named params, labels),
b) when we disable @a, then we should implement only one feature - named params
c) @b isn't compatible with SQL/XML that is implemented now

I don't found any notice about db2 default parameters.

Named params needs different algorithm of searching in pg_proc. There
should be some new problems - like

create function foo(a integer, b integer);
select foo(10,10); -- ok
select foo(a => 10, b =>20); -- ok
select foo(b=>20, a =>20); -- ok
select foo(c=>20, 20); -- unknown fce !!!

Regards
Pavel Stehule

real gram implemenation:
param_list:    param                               {                                       $$ = list_make1($1);
                     }                       | param_list ',' param                               {
                 $$ = lappend($1, $3);                               }               ;
 

param:               a_expr                               {                                       $$ = $1;
                }               | param_name POINTER a_expr                               {
         $$ = $3;                               }               | a_expr AS ColLabel                               {
                                  $$ = $1;                               }               | param_name POINTER a_expr AS
ColLabel                              {                                       $$ = $3;                               }
            ;
 


lexer
identifier              {ident_start}{ident_cont}*

typecast                "::"
pointer                 "=>"


Re: proposal sql: labeled function params

From
Tom Lane
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> I understand now why Oracle use => symbol for named params. This isn't
> used so operator - so implementation is trivial.

You really didn't understand the objection at all, did you?

The point is not about whether there is any built-in operator named =>.
The point is that people might have created user-defined operators named
that.
        regards, tom lane


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> I understand now why Oracle use => symbol for named params. This isn't
>> used so operator - so implementation is trivial.
>
> You really didn't understand the objection at all, did you?
>
> The point is not about whether there is any built-in operator named =>.
> The point is that people might have created user-defined operators named
> that.

I understand well, so only I don't see better solution. Yes, everyone
who used => should have problems, but it is similar with .. new
keywords, etc. Probably easy best syntax doesn't exist :(. I  haven't
idea who use => now and how often, and if this feature is possible in
pg, but there are not technical barriers.

regards
Pavel Stehule


>
>                        regards, tom lane
>


Re: proposal sql: labeled function params

From
"Asko Oja"
Date:
Would AS be harder to implement?

select foo(10 AS a, 20 AS b);
select foo(20 AS b, 20 AS a);
select x(0 >= 1 AS a);

other fantasies
select foo(10 a, 20 b);
select foo("a" 10, "b" 20);

regards,
Asko

On Wed, Aug 20, 2008 at 4:26 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2008/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> I understand now why Oracle use => symbol for named params. This isn't
>> used so operator - so implementation is trivial.
>
> You really didn't understand the objection at all, did you?
>
> The point is not about whether there is any built-in operator named =>.
> The point is that people might have created user-defined operators named
> that.

I understand well, so only I don't see better solution. Yes, everyone
who used => should have problems, but it is similar with .. new
keywords, etc. Probably easy best syntax doesn't exist :(. I  haven't
idea who use => now and how often, and if this feature is possible in
pg, but there are not technical barriers.

regards
Pavel Stehule


>
>                        regards, tom lane
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/21 Asko Oja <ascoja@gmail.com>:
> Would AS be harder to implement?
>
> select foo(10 AS a, 20 AS b);
> select foo(20 AS b, 20 AS a);
> select x(0 >= 1 AS a);
>
> other fantasies
> select foo(10 a, 20 b);
> select foo("a" 10, "b" 20);

no, I have it. Problem is in semantic. There are two features, that
fight about this keyword - named params and labeled params. I don't
thing so using AS for named params is good idea - mainly it's out of
standard (if I should count with SQL/XML) and out of tradition.

regards
Pavel

p.s. => is clean and readable, I thing it's better than space syntax
:)

>
> regards,
> Asko
>
> On Wed, Aug 20, 2008 at 4:26 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>
>> 2008/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
>> > "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> >> I understand now why Oracle use => symbol for named params. This isn't
>> >> used so operator - so implementation is trivial.
>> >
>> > You really didn't understand the objection at all, did you?
>> >
>> > The point is not about whether there is any built-in operator named =>.
>> > The point is that people might have created user-defined operators named
>> > that.
>>
>> I understand well, so only I don't see better solution. Yes, everyone
>> who used => should have problems, but it is similar with .. new
>> keywords, etc. Probably easy best syntax doesn't exist :(. I  haven't
>> idea who use => now and how often, and if this feature is possible in
>> pg, but there are not technical barriers.
>>
>> regards
>> Pavel Stehule
>>
>>
>> >
>> >                        regards, tom lane
>> >
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
>


Re: proposal sql: labeled function params

From
Decibel!
Date:
On Aug 20, 2008, at 8:26 AM, Pavel Stehule wrote:
> 2008/8/20 Tom Lane <tgl@sss.pgh.pa.us>:
>> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>>> I understand now why Oracle use => symbol for named params. This  
>>> isn't
>>> used so operator - so implementation is trivial.
>>
>> You really didn't understand the objection at all, did you?
>>
>> The point is not about whether there is any built-in operator  
>> named =>.
>> The point is that people might have created user-defined operators  
>> named
>> that.
>
> I understand well, so only I don't see better solution. Yes, everyone
> who used => should have problems, but it is similar with .. new
> keywords, etc. Probably easy best syntax doesn't exist :(. I  haven't
> idea who use => now and how often, and if this feature is possible in
> pg, but there are not technical barriers.


How about we poll -general and see what people say? I'll bet Tom a  
beer that no one replies saying they've created a => operator (unless  
maybe PostGIS uses it).

If we're really worried about it we can have a GUC for a few versions  
that turns off named parameter assignment. But I don't think we  
should compromise the design on the theory that some folks might be  
using that as an operator *and* can't change their application to  
wrap it's use in ().
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828



Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Thu, 2008-08-21 at 23:41 -0500, Decibel! wrote:
> On Aug 20, 2008, at 8:26 AM, Pavel Stehule wrote:

> How about we poll -general and see what people say? I'll bet Tom a  
> beer that no one replies saying they've created a => operator (unless  
> maybe PostGIS uses it).

Does Oracle use => for "labeled function params" or just named
arguments ?

> If we're really worried about it we can have a GUC for a few versions  
> that turns off named parameter assignment. But I don't think we  
> should compromise the design on the theory that some folks might be  
> using that as an operator *and* can't change their application to  
> wrap it's use in ().

I still think that better approach is allowing RECORD as input type and
do all the things Pavel proposed with a function that iterates over
record.

--------------
Hannu




Re: proposal sql: labeled function params

From
Teodor Sigaev
Date:
>> How about we poll -general and see what people say? I'll bet Tom a  
>> beer that no one replies saying they've created a => operator (unless  
>> maybe PostGIS uses it).
Hstore uses it:   * text => text - creates hstore type from two text strings

select 'a'=>'b';  ?column?
----------  "a"=>"b"


-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/
 


Re: Feeding results back into select (was: proposal sql: labeled function params)

From
"D'Arcy J.M. Cain"
Date:
On Sat, 23 Aug 2008 00:03:16 +0400
Teodor Sigaev <teodor@sigaev.ru> wrote:
> select 'a'=>'b';
>    ?column?
> ----------
>    "a"=>"b"

Branching the topic, I have a question about this.  I haven't studied
hstore extensively but this seems like a problem on it's face.
Shouldn't you be able to take the result of a select and pass it back
to a select?  I mean, what happens if you do this?

select "a"=>"b";

I suspect that you would get "ERROR:  column "a" does not exist" if you
do that.  What happens when you try to restore a dump?

I ran into a similar issue with my chkpass type (see contrib) where the
string inserted into the field is stored encrypted with functions to
test for equality basically like the Unix password model works.  If I
just displayed raw strings then a dump and reload would have trashed
all the passwords by re-encrypting them.  What I did was to make a
special case on input.  If the string started with ':' then I strip
that character and insert the string unchanged.  Then I changed the
output to prepend the ':'.  Now dump and reload work.

Just curious.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/22 Teodor Sigaev <teodor@sigaev.ru>:
>>> How about we poll -general and see what people say? I'll bet Tom a  beer
>>> that no one replies saying they've created a => operator (unless  maybe
>>> PostGIS uses it).
>
> Hstore uses it:
>   * text => text - creates hstore type from two text strings
>
> select 'a'=>'b';
>  ?column?
> ----------
>  "a"=>"b"
>
>

we should to have flag (or names are in pg_proc already), when
function allows named params -etc lot of system functions doesn't
named params. So everywhere where function hasn't defined names, then
=> symbol should by transformed to => operator.

???
Pavel

Pavel

> --
> Teodor Sigaev                                   E-mail: teodor@sigaev.ru
>                                                   WWW: http://www.sigaev.ru/
>


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/22 Hannu Krosing <hannu@2ndquadrant.com>:
> On Thu, 2008-08-21 at 23:41 -0500, Decibel! wrote:
>> On Aug 20, 2008, at 8:26 AM, Pavel Stehule wrote:
>
>> How about we poll -general and see what people say? I'll bet Tom a
>> beer that no one replies saying they've created a => operator (unless
>> maybe PostGIS uses it).
>
> Does Oracle use => for "labeled function params" or just named
> arguments ?
>

Oracle use it for named arguments - what I know, similar it doesn't
allow functionality as labeled params publicly - SQL/XML use it.

>> If we're really worried about it we can have a GUC for a few versions
>> that turns off named parameter assignment. But I don't think we
>> should compromise the design on the theory that some folks might be
>> using that as an operator *and* can't change their application to
>> wrap it's use in ().
>
> I still think that better approach is allowing RECORD as input type and
> do all the things Pavel proposed with a function that iterates over
> record.
>

record or hash table - it's implementation - second step. We have to
find syntax and semantic now.

Pavel

> --------------
> Hannu
>
>
>


Re: Feeding results back into select

From
Teodor Sigaev
Date:
>> select 'a'=>'b';
>>    ?column?
>> ----------
>>    "a"=>"b"



> 
> Branching the topic, I have a question about this.  I haven't studied
> hstore extensively but this seems like a problem on it's face.
> Shouldn't you be able to take the result of a select and pass it back
> to a select?  I mean, what happens if you do this?
> 

"a"=>"b" is a value of hstore type, so query should be:
select '"a"=>"b"'::hstore;

" character was chosen to simplify escaping,

> I ran into a similar issue with my chkpass type (see contrib) where the
> string inserted into the field is stored encrypted with functions to
> test for equality basically like the Unix password model works.  If I
> just displayed raw strings then a dump and reload would have trashed
> all the passwords by re-encrypting them.  What I did was to make a
> special case on input.  If the string started with ':' then I strip
> that character and insert the string unchanged.  Then I changed the
> output to prepend the ':'.  Now dump and reload work.

I see, but your problem is that password is one-way encrypted, so, you 
definitely need to distinguish already encrypted strings. Hstore hasn't that 
problem.




-- 
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
  WWW: http://www.sigaev.ru/
 


Re: proposal sql: labeled function params

From
Peter Eisentraut
Date:
On Friday 22 August 2008 07:41:30 Decibel! wrote:
> If we're really worried about it we can have a GUC for a few versions  
> that turns off named parameter assignment. But I don't think we  
> should compromise the design on the theory that some folks might be  
> using that as an operator *and* can't change their application to  
> wrap it's use in ().

Even if that were a reasonable strategy, you can't use GUC parameters to alter
parser behavior.


Re: Feeding results back into select

From
"D'Arcy J.M. Cain"
Date:
On Sat, 23 Aug 2008 14:04:30 +0400
Teodor Sigaev <teodor@sigaev.ru> wrote:
> >> select 'a'=>'b';
> >>    ?column?
> >> ----------
> >>    "a"=>"b"
> "a"=>"b" is a value of hstore type, so query should be:
> select '"a"=>"b"'::hstore;

Of course.  Now that I understand it's blindingly obvious that the
double quotes above are part of the string and the insert would be; INSERT INTO table (hfield) VALUES ('"a"=>"b"');

> " character was chosen to simplify escaping,

To prevent; INSERT INTO table (hfield) VALUES ('''a''=>''b''');

I guess my brain was pointed elsewhere when I asked.  Thanks for not
treating me like an idiot.  :-)

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/23 Peter Eisentraut <peter_e@gmx.net>:
> On Friday 22 August 2008 07:41:30 Decibel! wrote:
>> If we're really worried about it we can have a GUC for a few versions
>> that turns off named parameter assignment. But I don't think we
>> should compromise the design on the theory that some folks might be
>> using that as an operator *and* can't change their application to
>> wrap it's use in ().
>
> Even if that were a reasonable strategy, you can't use GUC parameters to alter
> parser behavior.
>

I thing, so it's possible - in this case. We should transform named
params to expr  after syntax analyze.

Pavel


Re: proposal sql: labeled function params

From
Gregory Stark
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:

> Hello
>
> 2008/8/23 Peter Eisentraut <peter_e@gmx.net>:
>> On Friday 22 August 2008 07:41:30 Decibel! wrote:
>>> If we're really worried about it we can have a GUC for a few versions
>>> that turns off named parameter assignment. But I don't think we
>>> should compromise the design on the theory that some folks might be
>>> using that as an operator *and* can't change their application to
>>> wrap it's use in ().
>>
>> Even if that were a reasonable strategy, you can't use GUC parameters to alter
>> parser behavior.
>
> I thing, so it's possible - in this case. We should transform named
> params to expr  after syntax analyze.

So for a bit of useless syntactic sugar we should introduce conflicts with
named parameters, conflicts with operators, introduce an un-sqlish syntax and
remove a feature users have already made use of and introduce backwards
compatibility issues for those users?

At any point in this discussion has anyone explained why these labels would
actually be a good idea?

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!


Re: proposal sql: labeled function params

From
Tom Lane
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> I thing, so it's possible - in this case. We should transform named
> params to expr  after syntax analyze.

You're going to have a hard time making parentheses affect the behavior
if you do it that way.
        regards, tom lane


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sat, 2008-08-23 at 08:21 +0200, Pavel Stehule wrote:
> Hello
> 
> 2008/8/22 Hannu Krosing <hannu@2ndquadrant.com>:
> > On Thu, 2008-08-21 at 23:41 -0500, Decibel! wrote:
> >> On Aug 20, 2008, at 8:26 AM, Pavel Stehule wrote:
> >
> >> How about we poll -general and see what people say? I'll bet Tom a
> >> beer that no one replies saying they've created a => operator (unless
> >> maybe PostGIS uses it).
> >
> > Does Oracle use => for "labeled function params" or just named
> > arguments ?
> >
> 
> Oracle use it for named arguments - what I know, similar it doesn't
> allow functionality as labeled params publicly - SQL/XML use it.
> 
> >> If we're really worried about it we can have a GUC for a few versions
> >> that turns off named parameter assignment. But I don't think we
> >> should compromise the design on the theory that some folks might be
> >> using that as an operator *and* can't change their application to
> >> wrap it's use in ().
> >
> > I still think that better approach is allowing RECORD as input type and
> > do all the things Pavel proposed with a function that iterates over
> > record.
> >
> 
> record or hash table - it's implementation - second step. We have to
> find syntax and semantic now.

Why not just use some standard record syntax, like 

SELECT(value::type name, ...)

or perhaps some extended ROW() or VALUES() syntax ?

Like this : SELECT * FROM FUNC(SELECT(value::type name, ...) AS r);

-----------------
Hannu




Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/23 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> I thing, so it's possible - in this case. We should transform named
>> params to expr  after syntax analyze.
>
> You're going to have a hard time making parentheses affect the behavior
> if you do it that way.
>
>                        regards, tom lane
>

I don't prefer this way
regards
Pavel Stehule


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
> So for a bit of useless syntactic sugar we should introduce conflicts with
> named parameters, conflicts with operators, introduce an un-sqlish syntax and
> remove a feature users have already made use of and introduce backwards
> compatibility issues for those users?
>
we talk only about "=>" symbol. What I googled, I never to find any
database that use AS for named params, and I don't really to create
next proprietary syntax (I would not to wait to ANSI). AS is usable,
but I don't think so it is good idea - it change sense of AS keyword
in SQL.

Oracle: fce (param => expr)
MSSQL: fce @param = expr
Firebird 2.0 allows defaults, but doesn't support named params


> At any point in this discussion has anyone explained why these labels would
> actually be a good idea?
>

it's allows smart libraries like SQL/XML
regards
Pavel Stehule

> --
>  Gregory Stark
>  EnterpriseDB          http://www.enterprisedb.com
>  Ask me about EnterpriseDB's 24x7 Postgres support!
>


Re: proposal sql: labeled function params

From
"Greg Stark"
Date:
<br /><p><font size="2">>><br /> >> At any point in this discussion has anyone explained why these <br />
>>labels would<br /> >> actually be a good idea?<br /> >><br /> ><br /> > it's allows smart
librarieslike SQL/XML<br /><br /> You could always just pass the label as an additional parameter. Which <br /> is all
thiswould be syntactic sugar for anyways. So it doesn't <br /> "allow" such libraries it just let users type
foo(bar=>'baz')instead <br /> of foo('bar','baz'). Not much gain for all this pain.<br /></font> 

Re: proposal sql: labeled function params

From
Tom Lane
Date:
Hannu Krosing <hannu@2ndQuadrant.com> writes:
> On Sat, 2008-08-23 at 08:21 +0200, Pavel Stehule wrote:
>> record or hash table - it's implementation - second step. We have to
>> find syntax and semantic now.

> Why not just use some standard record syntax, like 
> SELECT(value::type name, ...)

Yeah, that's one way.  It also strikes me that hstore itself provides a
usable solution to this problem, though only for simple-string values.
That is, you could do something like
create function myfunc(hstore) returns ...
select myfunc('tag1' => '42' || 'tag2' => 'foobar' || ...);

Or, with the new variadic function support,
create function myfunc(variadic hstore[]) returns ...
select myfunc('tag1' => '42', 'tag2' => 'foobar', ...);

which is just a couple of quote marks away from the syntax Pavel
wants.
        regards, tom lane


Re: proposal sql: labeled function params

From
daveg
Date:
On Sat, Aug 23, 2008 at 05:08:25PM +0100, Gregory Stark wrote:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 
> > Hello
> >
> > 2008/8/23 Peter Eisentraut <peter_e@gmx.net>:
> >> On Friday 22 August 2008 07:41:30 Decibel! wrote:
> >>> If we're really worried about it we can have a GUC for a few versions
> >>> that turns off named parameter assignment. But I don't think we
> >>> should compromise the design on the theory that some folks might be
> >>> using that as an operator *and* can't change their application to
> >>> wrap it's use in ().
> >>
> >> Even if that were a reasonable strategy, you can't use GUC parameters to alter
> >> parser behavior.
> >
> > I thing, so it's possible - in this case. We should transform named
> > params to expr  after syntax analyze.
> 
> So for a bit of useless syntactic sugar we should introduce conflicts with
> named parameters, conflicts with operators, introduce an un-sqlish syntax and
> remove a feature users have already made use of and introduce backwards
> compatibility issues for those users?
> 
> At any point in this discussion has anyone explained why these labels would
> actually be a good idea?

I was missing that too. What is this for that makes it so compelling?

-dg

-- 
David Gould       daveg@sonic.net      510 536 1443    510 282 0869
If simplicity worked, the world would be overrun with insects.


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/23 Hannu Krosing <hannu@2ndquadrant.com>:
> On Sat, 2008-08-23 at 08:21 +0200, Pavel Stehule wrote:
>> Hello
>>
>> 2008/8/22 Hannu Krosing <hannu@2ndquadrant.com>:
>> > On Thu, 2008-08-21 at 23:41 -0500, Decibel! wrote:
>> >> On Aug 20, 2008, at 8:26 AM, Pavel Stehule wrote:
>> >
>> >> How about we poll -general and see what people say? I'll bet Tom a
>> >> beer that no one replies saying they've created a => operator (unless
>> >> maybe PostGIS uses it).
>> >
>> > Does Oracle use => for "labeled function params" or just named
>> > arguments ?
>> >
>>
>> Oracle use it for named arguments - what I know, similar it doesn't
>> allow functionality as labeled params publicly - SQL/XML use it.
>>
>> >> If we're really worried about it we can have a GUC for a few versions
>> >> that turns off named parameter assignment. But I don't think we
>> >> should compromise the design on the theory that some folks might be
>> >> using that as an operator *and* can't change their application to
>> >> wrap it's use in ().
>> >
>> > I still think that better approach is allowing RECORD as input type and
>> > do all the things Pavel proposed with a function that iterates over
>> > record.
>> >
>>
>> record or hash table - it's implementation - second step. We have to
>> find syntax and semantic now.
>
> Why not just use some standard record syntax, like
>
> SELECT(value::type name, ...)
>
> or perhaps some extended ROW() or VALUES() syntax ?
>
> Like this :
>  SELECT * FROM FUNC(SELECT(value::type name, ...) AS r);
>

do you thing, so is it simpler?

Pavel

> -----------------
> Hannu
>
>
>


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/24 daveg <daveg@sonic.net>:
> On Sat, Aug 23, 2008 at 05:08:25PM +0100, Gregory Stark wrote:
>> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>>
>> > Hello
>> >
>> > 2008/8/23 Peter Eisentraut <peter_e@gmx.net>:
>> >> On Friday 22 August 2008 07:41:30 Decibel! wrote:
>> >>> If we're really worried about it we can have a GUC for a few versions
>> >>> that turns off named parameter assignment. But I don't think we
>> >>> should compromise the design on the theory that some folks might be
>> >>> using that as an operator *and* can't change their application to
>> >>> wrap it's use in ().
>> >>
>> >> Even if that were a reasonable strategy, you can't use GUC parameters to alter
>> >> parser behavior.
>> >
>> > I thing, so it's possible - in this case. We should transform named
>> > params to expr  after syntax analyze.
>>
>> So for a bit of useless syntactic sugar we should introduce conflicts with
>> named parameters, conflicts with operators, introduce an un-sqlish syntax and
>> remove a feature users have already made use of and introduce backwards
>> compatibility issues for those users?
>>
>> At any point in this discussion has anyone explained why these labels would
>> actually be a good idea?
>
> I was missing that too. What is this for that makes it so compelling?

We are talking about two features (or three).

1. Named params (and defaults for params) - allows call function with
less params or with params in different order. - default parameters
allows decrease size of libraries (number of functions). Using default
without named params isn't comfortable (Firebird 2 do it), so we would
named params - named params specifies "some param has value ...",
developer shouldn't specify all params. sure - this functionality is
syntactic sugar and are not necessary - it only increase developer's
comfort.

2. Labeled params - put some knowledge (labels) about used params into
function. This is usable for mainly for serialization and dump
function (typical real case is SQL/XML). Other case are custom
functions in MySQL -
http://www.mysqludf.org/lib_mysqludf_json/index.php , sure - this
functionality is syntactic sugar and are not necessary - it only
increase developer's comfort. Labels params mean "for this value use
label". It same as standard keyword "AS" do it. Labeled params allows
explicit labels or implicit labels (column names).

Regards
Pavel Stehule



>
> -dg
>
>
> --
> David Gould       daveg@sonic.net      510 536 1443    510 282 0869
> If simplicity worked, the world would be overrun with insects.
>


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/23 Tom Lane <tgl@sss.pgh.pa.us>:
> Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> On Sat, 2008-08-23 at 08:21 +0200, Pavel Stehule wrote:
>>> record or hash table - it's implementation - second step. We have to
>>> find syntax and semantic now.
>
>> Why not just use some standard record syntax, like
>> SELECT(value::type name, ...)
>
> Yeah, that's one way.  It also strikes me that hstore itself provides a
> usable solution to this problem, though only for simple-string values.
> That is, you could do something like
>
>        create function myfunc(hstore) returns ...
>
>        select myfunc('tag1' => '42' || 'tag2' => 'foobar' || ...);
>
> Or, with the new variadic function support,
>
>        create function myfunc(variadic hstore[]) returns ...
>
>        select myfunc('tag1' => '42', 'tag2' => 'foobar', ...);
>
> which is just a couple of quote marks away from the syntax Pavel
> wants.
>

it's not far. I am only doesn't know if is it labeled params or named
params :). Using hstore is usable, but I dislike it. There is small
overhead and would to use named params for classic functions - with
different types and fixed count of params. I am thinking so first step
is implementation of defaults without named params like firebird. It's
less controversy.

regards
Pavel Stehule

>                        regards, tom lane
>


Re: proposal sql: labeled function params

From
Hannu Krosing
Date:
On Sun, 2008-08-24 at 08:05 +0200, Pavel Stehule wrote:
> 2008/8/23 Tom Lane <tgl@sss.pgh.pa.us>:
> > Hannu Krosing <hannu@2ndQuadrant.com> writes:
> >> On Sat, 2008-08-23 at 08:21 +0200, Pavel Stehule wrote:
> >>> record or hash table - it's implementation - second step. We have to
> >>> find syntax and semantic now.
> >
> >> Why not just use some standard record syntax, like
> >> SELECT(value::type name, ...)
> >
> > Yeah, that's one way.  It also strikes me that hstore itself provides a
> > usable solution to this problem, though only for simple-string values.
> > That is, you could do something like
> >
> >        create function myfunc(hstore) returns ...
> >
> >        select myfunc('tag1' => '42' || 'tag2' => 'foobar' || ...);
> >
> > Or, with the new variadic function support,
> >
> >        create function myfunc(variadic hstore[]) returns ...
> >
> >        select myfunc('tag1' => '42', 'tag2' => 'foobar', ...);
> >
> > which is just a couple of quote marks away from the syntax Pavel
> > wants.
> >
> 
> it's not far. I am only doesn't know if is it labeled params or named
> params :).

This is "labeled params", or rather variadic hstore. done this way, it
has added benefit over single hstore param, that "key" or "label" can be
repeated:

select myfunc('name' => 'bob', 'age'=>'42', 'name' => 'bill', ...);

same as

select myfunc2(select('bob' as name, 42 as age, 'bill' as name, ...));

>  Using hstore is usable, but I dislike it. There is small
> overhead and would to use named params for classic functions - with
> different types and fixed count of params. I am thinking so first step
> is implementation of defaults without named params like firebird. It's
> less controversy.

-------------------
Hannu




Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/24 Hannu Krosing <hannu@2ndquadrant.com>:
> On Sun, 2008-08-24 at 08:05 +0200, Pavel Stehule wrote:
>> 2008/8/23 Tom Lane <tgl@sss.pgh.pa.us>:
>> > Hannu Krosing <hannu@2ndQuadrant.com> writes:
>> >> On Sat, 2008-08-23 at 08:21 +0200, Pavel Stehule wrote:
>> >>> record or hash table - it's implementation - second step. We have to
>> >>> find syntax and semantic now.
>> >
>> >> Why not just use some standard record syntax, like
>> >> SELECT(value::type name, ...)
>> >
>> > Yeah, that's one way.  It also strikes me that hstore itself provides a
>> > usable solution to this problem, though only for simple-string values.
>> > That is, you could do something like
>> >
>> >        create function myfunc(hstore) returns ...
>> >
>> >        select myfunc('tag1' => '42' || 'tag2' => 'foobar' || ...);
>> >
>> > Or, with the new variadic function support,
>> >
>> >        create function myfunc(variadic hstore[]) returns ...
>> >
>> >        select myfunc('tag1' => '42', 'tag2' => 'foobar', ...);
>> >
>> > which is just a couple of quote marks away from the syntax Pavel
>> > wants.
>> >
>>
>> it's not far. I am only doesn't know if is it labeled params or named
>> params :).
>
> This is "labeled params", or rather variadic hstore. done this way, it
> has added benefit over single hstore param, that "key" or "label" can be
> repeated:
>
> select myfunc('name' => 'bob', 'age'=>'42', 'name' => 'bill', ...);
>
> same as
>
> select myfunc2(select('bob' as name, 42 as age, 'bill' as name, ...));
>

and actually, how far we are from original proposal

select myfunc(name => 'bob', age => 42 ...

this syntax is most cleaner, and doesn't need bigger changes in
parser, zero changes in PL

regards
Pavel


>>  Using hstore is usable, but I dislike it. There is small
>> overhead and would to use named params for classic functions - with
>> different types and fixed count of params. I am thinking so first step
>> is implementation of defaults without named params like firebird. It's
>> less controversy.
>
> -------------------
> Hannu
>
>
>


Re: proposal sql: labeled function params

From
Tom Lane
Date:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2008/8/23 Hannu Krosing <hannu@2ndquadrant.com>:
>> Why not just use some standard record syntax, like

> do you thing, so is it simpler?

It's not about being "simpler", it's about pointing out that there are
ways to do what you need without creating compatibility problems and
without commandeering syntax that, if we were going to commandeer it,
would be far better used for named params.

IMHO, the use-case for labeled parameters is simply much too narrow
to justify giving them special syntax if there is any possible way
to avoid it.  We have now seen a couple of ways to do it without
new syntax, at the cost of a few more lines inside the called function
to examine its arguments.  But the use-cases you've suggested involve
functions that are complicated enough that that's not going to be any
big deal.

So I feel that the proposal for labeled parameters as such is dead
in the water, and that the only usefulness this thread has had is
(re-) exploring the syntactic alternatives available for named params.
        regards, tom lane


Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
2008/8/24 Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> 2008/8/23 Hannu Krosing <hannu@2ndquadrant.com>:
>>> Why not just use some standard record syntax, like
>
>> do you thing, so is it simpler?
>
> It's not about being "simpler", it's about pointing out that there are
> ways to do what you need without creating compatibility problems and
> without commandeering syntax that, if we were going to commandeer it,
> would be far better used for named params.
>
> IMHO, the use-case for labeled parameters is simply much too narrow
> to justify giving them special syntax if there is any possible way
> to avoid it.  We have now seen a couple of ways to do it without
> new syntax, at the cost of a few more lines inside the called function
> to examine its arguments.  But the use-cases you've suggested involve
> functions that are complicated enough that that's not going to be any
> big deal.
>
> So I feel that the proposal for labeled parameters as such is dead
> in the water, and that the only usefulness this thread has had is
> (re-) exploring the syntactic alternatives available for named params.

I feel it too.

Regards
Pavel Stehule

>
>                        regards, tom lane
>


Re: proposal sql: labeled function params

From
Martijn van Oosterhout
Date:
On Sun, Aug 24, 2008 at 12:00:01PM -0400, Tom Lane wrote:
> So I feel that the proposal for labeled parameters as such is dead
> in the water, and that the only usefulness this thread has had is
> (re-) exploring the syntactic alternatives available for named params.

FWIW, I think the way that python manages named and labelled params in
a single calling syntax fairly straightforward.

http://docs.python.org/tut/node6.html#SECTION006720000000000000000

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: proposal sql: labeled function params

From
"Pavel Stehule"
Date:
Hello

2008/8/24 Martijn van Oosterhout <kleptog@svana.org>:
> On Sun, Aug 24, 2008 at 12:00:01PM -0400, Tom Lane wrote:
>> So I feel that the proposal for labeled parameters as such is dead
>> in the water, and that the only usefulness this thread has had is
>> (re-) exploring the syntactic alternatives available for named params.
>
> FWIW, I think the way that python manages named and labelled params in
> a single calling syntax fairly straightforward.
>
> http://docs.python.org/tut/node6.html#SECTION006720000000000000000
>

I will work on default params first and I return to named params later.

regards
Pavel


> Have a ncie day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
>> Please line up in a tree and maintain the heap invariant while
>> boarding. Thank you for flying nlogn airlines.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIsalEIB7bNG8LQkwRAu1RAJ0TM9/JMUXZI/A+EtNeKp1KHku4OACfQjVa
> QLr4uM/tzgc2y3lodMi2EnU=
> =OSzx
> -----END PGP SIGNATURE-----
>
>