Re: proposal sql: labeled function params - Mailing list pgsql-hackers

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