Re: proposal sql: labeled function params

2008/8/14 Hannu Krosing <hannu(at)2ndquadrant(dot)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
>
>
>
>
>
>
>