Thread: ERROR: 42601: a column definition list is only allowed for functions returning "record"

Hi,

I’m running postgres following version on win2K server (PostgreSQL 8.1.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special))

I am using c# and npgsql 1.0 (same behaviour with 0.7) to call a function that has 6 input parameters and 4 output parameters.

When I call .ExecuteNonQuery(), I get the following message

ERROR: 42601: a column definition list is only allowed for functions returning "record"

Here is my function declaration

CREATE or REPLACE FUNCTION public.usp_hello(

IN centrex float8,

IN centrey float8,

IN pixelwidth int4,

IN pixelheight int4,

IN originaldataset text,

IN originallevel int4,

IN srid int4,

OUT newdataset text,

OUT newlevel int4,

OUT newx float8,

OUT newy float8) 

returns record AS

My calling c# code is as follows

NpgsqlCommand sqlCommand1 = new NpgsqlCommand();                 

                  sqlCommand1.CommandText = "usp_hello";

                  sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;

                  sqlCommand1.Connection = sqlConnection1;

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("centrex",NpgsqlTypes.NpgsqlDbType.Double));

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("centrey",NpgsqlTypes.NpgsqlDbType.Double));

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("pixelwidth",NpgsqlTypes.NpgsqlDbType.Integer));

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("pixelheight",NpgsqlTypes.NpgsqlDbType.Integer));

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("originaldataset",NpgsqlTypes.NpgsqlDbType.Text));

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("originallevel",NpgsqlTypes.NpgsqlDbType.Integer));                

                  sqlCommand1.Parameters.Add(new NpgsqlParameter("srid",NpgsqlTypes.NpgsqlDbType.Integer));

                  sqlCommand1.Parameters["centrex"].Value = centreX;

                  sqlCommand1.Parameters["centrey"].Value = centreY;

                  sqlCommand1.Parameters["pixelwidth"].Value = pixelWidth;

                  sqlCommand1.Parameters["pixelheight"].Value = pixelHeight;

                  sqlCommand1.Parameters["originaldataset"].Value = originalDataset;

                  sqlCommand1.Parameters["originallevel"].Value = originalLevel;

                  sqlCommand1.Parameters["srid"].Value = 27700;

                  NpgsqlParameter newds = new NpgsqlParameter("newdataset",NpgsqlTypes.NpgsqlDbType.Text);                 

                  newds.Direction = ParameterDirection.Output;

                  sqlCommand1.Parameters.Add(newds);

                  NpgsqlParameter newlvl = new NpgsqlParameter("newlevel",NpgsqlTypes.NpgsqlDbType.Integer);                

                  newlvl.Direction = ParameterDirection.Output;

                  sqlCommand1.Parameters.Add(newlvl);

                  NpgsqlParameter newx = new NpgsqlParameter("newx",NpgsqlTypes.NpgsqlDbType.Double);

                  newx.Direction = ParameterDirection.Output;

                  sqlCommand1.Parameters.Add(newx);

                  NpgsqlParameter newy = new NpgsqlParameter("newy",NpgsqlTypes.NpgsqlDbType.Double);

                  newy.Direction = ParameterDirection.Output;

                  sqlCommand1.Parameters.Add(newy);

                  try

                  {

                        sqlConnection1.Open();

                        sqlCommand1.ExecuteNonQuery();                       

                        newDataset = sqlCommand1.Parameters["newdataset"].Value.ToString();

                        newLevel = Convert.ToInt32(sqlCommand1.Parameters["newlevel"].Value);

                  }                

I cannot find any other matching issues, please help!

Gopal