CODEWITHSUNDEEP
arrayspostgresqlfunction
Vineet
Vineet

Reputation: 897

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

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.

Upvotes: 0

Views: 168

Answers (1)

Abelisto
Abelisto

Reputation: 15624

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!';

Upvotes: 1

Related Questions