How to pass custom typed multi-dimmensional array as a function parameter in PostgreSQL?

Question

I've made a simple custom type:

CREATE TYPE public."FriendDetails" AS
   ("Email" character varying,
    "Name" character varying);
ALTER TYPE public."FriendDetails"
  OWNER TO postgres;

To be used in the following function (It just returns a string - contact!):

CREATE OR REPLACE FUNCTION public."addFriend"(
    "GroupName" character varying,
    friends "FriendDetails"[])
  RETURNS character varying AS
$BODY$BEGIN
    RETURN "contact!";
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public."addFriend"(character varying, "FriendDetails"[])
  OWNER TO postgres;

The above code was generated using pgAdminIII.

I tried to query it as follows:

select addFriend('Champs',ARRAY[['qwe','asd'],['zxc','rty']]::public."FriendDetails"[]);

And got an error:

ERROR:  malformed record literal: "qwe"
LINE 1: select addFriend('Champs',ARRAY[['qwe','asd'],['zxc','rty']]...
                                         ^
DETAIL:  Missing left parenthesis.
********** Error **********

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

I tried substituting curly braces ({}), and losing the ARRAY, but nothing seems to work - they'll resulted in syntax errors.

Can someone kindly show how the syntax should be to invoke this method using the (1) curly braces and (2) by using the ARRAY constructor.

Composite Value Input

Abelisto
– Abelisto

2016-03-04 18:37:53 +00:00
Commented Mar 4, 2016 at 18:37 — Abelisto
– Abelisto, Commented Mar 4, 2016 at 18:37

Abelisto · Accepted Answer · 2016-03-04 18:50:01Z

1

There are errors in your code:

Use round brackets for row/record types constants:

ARRAY[('qwe','asd'),('zxc','rty')]

Add doublequotes to function name:

select "addFriend"('Champs',ARRAY[('qwe','asd'),('zxc','rty')]::public."FriendDetails"[]);

Use singlequotes for string constants:

RETURN 'contact!';

answered Mar 4, 2016 at 18:50

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vineet Over a year ago

Thank you. This has been a real help. I also understand we can write the same without the ARRAY syntax. Using only the {}. Could you show me how?

Abelisto Over a year ago

@Vineet select "addFriend"('Champs','{"(qwe,asd)","(zxc,rty)"}'); - the simplest way.

Vineet Over a year ago

Thank you once again. :)

Vineet Over a year ago

Could you help answer this one?

Collectives™ on Stack Overflow

How to pass custom typed multi-dimmensional array as a function parameter in PostgreSQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related