Re: Custom types as parameter in stored function

On Mon, Jun 27, 2011 at 4:33 AM, mephysto <gennaria(at)email(dot)it> wrote:
> Hello to everyone,
> I am trying to pass custom types as parameters in stored functions, and I
> tried this syntax successfully:
>
> create type myType as (id bigint, name character varying);
>
>
> create or replace myfunc(obj myType)
> returns void as
> begin
> .......
> end;
>
>
> select myfunc((1, 'foo')::myType;
>
>
> In this manner, if I understand it, there is a positional assignment of
> attribute value: id = 1 and name = foo.
> My ask is is there a manner to assing value to attribute of custom type by
> name instead by position.

You can do it via hstore in 9.0+...just be aware it is slower than the
row constructor method. See the example below -- note CREATE
EXTENSION is a 9.1 feature -- to do it in 9.0 you have to install the
contrib script manually.

postgres=# create extension hstore;
WARNING: => is deprecated as an operator name
DETAIL: This name may be disallowed altogether in future versions of
PostgreSQL.
CREATE EXTENSION

postgres=# create type t as (a int, b text);
CREATE TYPE

postgres=# select populate_record(null::t, 'b=>9, a=>2');
populate_record
-----------------
(2,9)

merlin