Re: proposal sql: labeled function params

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(at)2ndquadrant(dot)com>:
> > On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote:
> >> Hannu Krosing <hannu(at)2ndQuadrant(dot)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
> >
> >
> >
>