Thread: Custom types as parameter in stored function

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.

Thanks in advance.

Mephysto.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Custom-types-as-parameter-in-stored-function-tp4527618p4527618.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Jun 27, 2011 at 4:33 AM, mephysto <gennaria@email.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

Thank you Merlin.

Now, Can I pass the select as an argument of my function?

Or must I do in other manner?

Sorry, but I am a novice.

Best regards.

Mephysto

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Custom-types-as-parameter-in-stored-function-tp4527618p4529531.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Jun 27, 2011 at 2:22 PM, mephysto <gennaria@email.it> wrote:
> Thank you Merlin.
>
> Now, Can I pass the select as an argument of my function?

sure:
select myfunc(populate_record(null::t, 'b=>9, a=>2'));

merlin