proposal sql: labeled function params

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

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

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

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

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?

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

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.

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

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

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

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

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

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

"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

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.

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 .

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

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

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