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

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