Devart Forums

Posted: **Fri 25 May 2012 22:15**

I have written a couple of stored functions in my database that take a UUID and an array of UUIDs as parameters. They compile fine in PostgreSQL.

When I try to update my database model in Entity Developer and include these functions in the model, I get the error:

Code: Select all

The Type ARRAY is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.

How do I fix the model to get rid of the error?

Here is the code for one of the functions:

Code: Select all

CREATE OR REPLACE FUNCTION security.SynchRolesForGroup(grpid uuid, roleids uuid[]) RETURNS void AS
$BODY$
DECLARE
    RoleId      UUID;
BEGIN
    DELETE FROM security.aspnetx_RolesInGroups WHERE GroupId = GrpId;

    -- Now loop over all of the UUIDs in the RoleIds parameter
    FOREACH RoleId IN ARRAY RoleIds LOOP
        INSERT INTO security.aspnetx_RolesInGroups
            ( GroupId, RoleId )
        VALUES
            ( GrpId, RoleId );
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

Tony

Posted: **Mon 28 May 2012 14:37**

Entity Framework supports only primitive types: http://msdn.microsoft.com/en-us/library/ee382832.aspx. From the Entity Framework's point of view, an array is not a primitive type.

Posted: **Tue 29 May 2012 12:41**

Thank you for getting back to me so promptly.

Would I be able to call the stored function through PgSqlCommand? Is there a way I can pass an array as a parameter to the stored function using PgSqlCommand? Something like

Code: Select all

Guid[] ids = new Guid[] { . . . };
using ( PgSqlCommand cmd = new PgSqlCommand( "SELECT function( ids );" ) {
    cmd.Parameters.AddWithValue( "ids", ids );

    PgSqlReader reader = cmd.ExecuteReader();
    while ( reader.Read() ) {
        ... 
    }
}

Thanks

Tony

Posted: **Wed 30 May 2012 12:27**

TonyV wrote:Would I be able to call the stored function through PgSqlCommand? Is there a way I can pass an array as a parameter to the stored function using PgSqlCommand?

Yes, it is possible. Here is an example of how it can be done.
Script for table creation:

Code: Select all

CREATE TABLE dept
(
  deptno integer NOT NULL,
  dname character varying(14),
  loc character varying(13),
  CONSTRAINT dept_pkey PRIMARY KEY (deptno)
);

And the following stored procedure will insert rows into the dept table:

Code: Select all

create or replace function dept_multi_insert(id integer[], name text[], loc text[])
returns void as'
declare
i int:=1;
length int:=array_length(id,1);
begin
while i<length+1 loop
insert into dept (deptno, dname, loc) values (id[i], name[i], loc[i]);
i:=i+1;
end loop;
end
 ' LANGUAGE 'plpgsql';

We can create 3 array parameters - one for each column, fill them and execute the stored procedure:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("host=***;port=***;user id=***;password=***;");
conn.Open();

object[] arr_id = new object[3] { 1008, 1009, 1010 };
object[] arr_name = new object[3] { "name1", "name2", "name3" };
object[] arr_loc = new object[3] { "Vegas", "Hollywood", "NY" };

PgSqlArray id = new PgSqlArray(arr_id, PgSqlType.Int, 1, 3);
PgSqlArray name = new PgSqlArray(arr_name,PgSqlType.Text, 1, 3);
PgSqlArray loc = new PgSqlArray(arr_loc,PgSqlType.Text, 1, 3);

PgSqlCommand comm = new PgSqlCommand("dept_multi_insert", conn);
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.Add("id", PgSqlType.Array).Value = id;
comm.Parameters.Add("name", PgSqlType.Array).Value = name;
comm.Parameters.Add("loc", PgSqlType.Array).Value = loc;
comm.ExecuteNonQuery();
conn.Close();

After executing this code, three rows will be inserted into the dept table:

Code: Select all

1008	name1	Vegas
1009	name2	Hollywood
1010	name3	NY

Posted: **Wed 30 May 2012 12:34**

Thank you very much! I believe we can make this work. I can probably call the stored function through the context's ExeciteStoreCommand method, so I won't have to create the PgSqlConnection and PgSqlCommand objects.

Tony

Devart Forums

Does Entity Developer support array parameters to stored fun

Does Entity Developer support array parameters to stored fun

Re: Does Entity Developer support array parameters to stored

Re: Does Entity Developer support array parameters to stored

Re: Does Entity Developer support array parameters to stored

Re: Does Entity Developer support array parameters to stored