存储过程- PostgreSQL错误22P02无效的整数输入语法

Question

我一直得到22P02错误:invalid input syntax for integer: "{1,2,3}"(见服务呼叫中的评论)。

这是我的服务电话:

...
using (var command = new NpgsqlCommand("mediabase.create_media", connection))
{
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add("title", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.title;
    command.Parameters.Add("uniqueFilename", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.uniqueFilename;
    command.Parameters.Add("description", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.description;
    command.Parameters.Add("categoryIds", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.categoryIds; /* here the value is "1,2,3" */
    command.Parameters.Add("typeId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.typeId;
    command.Parameters.Add("ownerId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.ownerId;
    command.Parameters.Add("statusId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.statusId;
    command.Parameters.Add("gpsLocation", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.gpsLocation;
    command.Parameters.Add("locationNameId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.locationId;
    command.Parameters.Add("uploadUserId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.uploadUserId;
    command.Parameters.Add("uploadDate", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.uploadDate;
    command.Parameters.Add("uploadIp", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.uploadIp;
    command.Parameters.Add("metadataId", NpgsqlTypes.NpgsqlDbType.Integer).Value = metadataId;
    command.Parameters.Add("sysEnvironment", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.sysEnvironment;
    command.Parameters.Add("languageId", NpgsqlTypes.NpgsqlDbType.Integer).Value = media.languageId;
    command.Parameters.Add("publicationIds", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.publicationIds;
    command.Parameters.Add("limitations", NpgsqlTypes.NpgsqlDbType.Varchar).Value = media.limitations;
    mediaId = Convert.ToInt32(command.ExecuteScalar());
}
pgTransaction.Commit();
...

和存储过程:

CREATE OR REPLACE FUNCTION mediabase.create_media(
    title character varying, 
    uniquefilename character varying, 
    description character varying, 
    categoryids character varying, 
    typeid integer, 
    ownerid integer, 
    statusid integer, 
    gpslocation character varying, 
    locationnameid integer, 
    uploaduserid integer, 
    uploaddate character varying, 
    uploadip character varying, 
    metadataid integer, 
    sysenvironment character varying, 
    languageid integer, 
    publicationids character varying, 
    limitations character varying)
  RETURNS integer AS
$BODY$
    declare _mediaId integer;
    declare _point varchar;
    declare _gps_location geometry;
    declare _id text;
    begin
    IF (gpslocation <> '') THEN
      _point = 'POINT(' || gpslocation || ')';
      _gps_location = ST_Transform(ST_GeomFromText(_point, 4326), 900913);
    ELSE
      _gps_location = NULL;
    END IF;
    insert into mediabase.media (
        title, 
        unique_filename, 
        description, 
        owner_id, 
        status_id, 
        gps_location, 
        type_id, 
        location_name_id, 
        upload_user_id, 
        upload_date, 
        upload_ip, 
        metadata_id, 
        system_environment,
        language_id,
        limitations)
    values (
        title, 
        uniqueFilename, 
        description, 
        ownerId, 
        statusId, 
        _gps_location, 
        typeId, 
        locationNameId, 
        uploadUserId, 
        uploadDate, 
        uploadIp, 
        metadataId, 
        sysEnvironment,
        languageid,
        limitations)
    returning id into _mediaId; 
    -- insert category ids
    FOR _id IN SELECT string_to_array (categoryids,',')
    LOOP 
        INSERT into mediabase.media_categories (media_id, category_id)
        values (_mediaId, (_id::int));
    END LOOP;
    -- insert publication ids
    FOR _id IN SELECT string_to_array (publicationids,',')
    LOOP 
        INSERT into mediabase.media_publications (media_id, publication_id)
        values (_mediaId, (_id::int));
    END LOOP;
    return _mediaId;
    end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

在业务方法中可以看到，参数categoryids的输入是字符串"1,2,3"，参数NpgsqlDbType的输入是字符串Varchar。在存储过程中，输入类型是character varying。

下面是执行日志:

2013-01-17 08:56:42 CET ERROR:  invalid input syntax for integer: "{1,2,3}"
2013-01-17 08:56:42 CET CONTEXT:  SQL statement "INSERT into mediabase.media_categories (media_id, category_id)
            values (_mediaId, (_id::integer))"
    PL/pgSQL function "create_media" line 54 at SQL statement
2013-01-17 08:56:42 CET STATEMENT:  select * from mediabase.create_media(('Penguins')::varchar,('079117ec-676f-4022-9950-69e55c2a2600_Penguins.jpg')::varchar,('Description...')::varchar,('1,2,3')::varchar,(1)::int4,(1)::int4,(1)::int4,('')::varchar,(1)::int4,(1)::int4,('17/01/2013 08:56:42')::varchar,('::1')::varchar,(399)::int4,('dev')::varchar,(1)::int4,('1,2')::varchar,('Limitations...')::varchar)

为什么会出现这个错误?

Answer 1

我已经用@dezso和@CraigRinger技巧解决了这个问题。我现在用的是整数数组而不是逗号分隔的字符串。修改如下:

服务呼叫:

...
/* media.categoryIds is of type int[] now */
command.Parameters.Add("categoryIds", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer).Value = media.categoryIds;
...

在存储过程上:

...
CREATE OR REPLACE FUNCTION mediabase.create_media(
    title character varying, 
    uniquefilename character varying, 
    description character varying, 
    categoryids integer[], -- changed type
    typeid integer, 
    ownerid integer, 
    statusid integer, 
    gpslocation character varying, 
    locationnameid integer, 
    uploaduserid integer, 
    uploaddate character varying, 
    uploadip character varying, 
    metadataid integer, 
    sysenvironment character varying, 
    languageid integer, 
    publicationids integer[], -- changed type
    limitations character varying)
...
...
    declare i integer;
...
...
    -- insert category ids
    FOR i IN SELECT generate_subscripts( categoryids, 1 )
    LOOP 
        INSERT into mediabase.media_categories (media_id, category_id)
        values (_mediaId, categoryids[i]);
    END LOOP;
    -- insert publication ids
    FOR i IN SELECT generate_subscripts( publicationids, 1 )
    LOOP 
        INSERT into mediabase.media_publications (media_id, publication_id)
        values (_mediaId, publicationids[i]);
    END LOOP;
...

再次感谢你的帮助。