Lists: | pgsql-general |
---|
From: | mephysto <gennaria(at)email(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Custom types as parameter in stored function |
Date: | 2011-06-27 09:33:27 |
Message-ID: | 1309167207054-4527618.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | mephysto <gennaria(at)email(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Custom types as parameter in stored function |
Date: | 2011-06-27 09:47:44 |
Message-ID: | 1309168064709-4527663.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Ehmmm, sorry..
Correct syntax for select is:
select myfunc((1, 'foo')::myType);
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Custom-types-as-parameter-in-stored-function-tp4527618p4527663.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | mephysto <gennaria(at)email(dot)it> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Custom types as parameter in stored function |
Date: | 2011-06-27 16:02:03 |
Message-ID: | BANLkTinfYioW8odbq2EAnP4AgFx3ubnY8Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | mephysto <gennaria(at)email(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Custom types as parameter in stored function |
Date: | 2011-06-27 19:22:36 |
Message-ID: | 1309202556276-4529531.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | mephysto <gennaria(at)email(dot)it> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Custom types as parameter in stored function |
Date: | 2011-06-27 19:41:47 |
Message-ID: | BANLkTi=vsSS0QWMH5OzWLdaXLfhQMqxOjQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, Jun 27, 2011 at 2:22 PM, mephysto <gennaria(at)email(dot)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