错误：查询没有目标函数的数据返回fint的bigint

问题描述

关于此错误，在SO上有许多类似的问题，但似乎没有一个可以回答我的情况。我在PostgreSql中创建了以下存储过程：

There are many similar questions on SO about this error, but none seem to answer my case. I have created the following stored procedure in PostgreSql:

CREATE OR REPLACE FUNCTION "MySchema".UserAccountInsert(
    id             bigint,
    lang           varchar(3),
    nname          varchar(40),
    email          varchar(40),
    email_conf     boolean,
    status         smallint,
    status_update  bigint,
    creation       bigint,
    preferences    json)
    RETURNS bigint AS $BODY$
DECLARE
    rowc INTEGER;
    ret_id bigint;
BEGIN
    SELECT "ID" FROM "MySchema"."USER_ACCOUNT"
        WHERE "ID" = id OR "NAME" = nname OR "EMAIL" = email
        LIMIT 1;
    GET DIAGNOSTICS rowc = ROW_COUNT;
    IF ( rowc > 0 ) THEN
        ret_id = -1; /* Unsuccessful */
    ELSE
        IF ( id <= 0 ) THEN
            INSERT INTO "MySchema"."USER_ACCOUNT" ("LANG","NAME","EMAIL","EMAIL_CONF","STATUS","STATUS_UPDATE","CREATION","PREFERENCES")
            VALUES (lang,nname,email,email_conf,status,status_update,creation,preferences) RETURNING "ID" INTO ret_id;
        ELSE
            INSERT INTO "MySchema"."USER_ACCOUNT" ("ID", "LANG","NAME","EMAIL","EMAIL_CONF","STATUS","STATUS_UPDATE","CREATION","PREFERENCES")
            VALUES (id, lang,nname,email,email_conf,status,status_update,creation,preferences);
            ret_id = id;
        END IF;
    END IF;
    RETURN ret_id;
END; $BODY$
LANGUAGE plpgsql;

我正试图从pgAdmin III中调用它，并带有：

and I am trying to call it from pgAdmin III with:

SELECT "MySchema".UserAccountInsert(
    1000::bigint,
    'ENG'::varchar(3),
    'name1000'::varchar(40),
    'email1000'::varchar(40),
    'f'::boolean,
    1::smallint,
    1391878008121::bigint,
    1391878008121::bigint,
    '{}'::json) as ret_id;

但我收到以下错误消息：

but I am getting the following error message:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "MySchema".useraccountinsert(bigint,character varying,character varying,character varying,boolean,smallint,bigint,bigint,json) line 6 at SQL statement

我应该如何调用我的函数？

How should I call my function?

推荐答案

在a_horse_with_no_name的注释之后，我对我的函数进行了如下修改：

Following a_horse_with_no_name's comment, I have modified my function as following:

SELECT COUNT(*) INTO rowc FROM "MySchema"."USER_ACCOUNT"
    WHERE "ID" = id OR "NAME" = nname OR "EMAIL" = email
    LIMIT 1;
/* GET DIAGNOSTICS rowc = ROW_COUNT; */

而且有效！

这篇关于错误：查询没有目标函数的数据返回fint的bigint的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！