Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016

    Unanswered: Question on unnest types

    I created a sample type as follows as used that in the function

    create type newtype AS (id integer, value integer);

    create FUNCTION newfun(t newtype[])
    returns SETOF newtype as

    select unnest(t::newtype[])

    When I call this function as select * from newfun('{2,1}')
    I get below error. I think I may not passing the right format.

    ERROR: malformed record literal: "2"
    LINE 1: select * from newfun('{2}')
    DETAIL: Missing left parenthesis.
    ********** Error **********

    ERROR: malformed record literal: "2"
    SQL state: 22P02
    Detail: Missing left parenthesis.
    Character: 22


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    The constant '{2,1}' specifies an array with two (integer) elements. It is equivalent to array[2,1]. Both yield an integer[]

    But your function expects an array of newtype records. To create an instance of a newtype record you need to use row(2,1)::newtype, to pass an array of newtype elements you need to use array[row(1,1), row(2,1)]::newtype[]

    It is enough to cast the array to newtype[] you don't need to cast each element separately.

    So to call your function with two records in the array, you need to use:
    select * 
    from newfun(array[row(1,1), row(2,1)]::newtype[])
    But the real question is: what problem are you trying to solve with this? This seems like a rather strange setup.

    And please do learn how to use the [code] tag.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

  3. #3
    Join Date
    Jan 2016
    I have few stored procedures in sql server that are using table value pair(in SQL they are user type as table with 2 columns). So my procedure is expecting that type as input.
    I am trying similar pattern in postgres.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts