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

Search Discussions

  • Mephysto at Jun 27, 2011 at 9:47 am
    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.
  • Merlin Moncure at Jun 27, 2011 at 4:02 pm

    On Mon, Jun 27, 2011 at 4:33 AM, mephysto 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
  • Mephysto at Jun 27, 2011 at 7:22 pm
    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.
  • Merlin Moncure at Jun 27, 2011 at 7:41 pm

    On Mon, Jun 27, 2011 at 2:22 PM, mephysto 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 27, '11 at 9:47a
activeJun 27, '11 at 7:41p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Mephysto: 3 posts Merlin Moncure: 2 posts

People

Translate

site design / logo © 2021 Grokbase