Question

我正试图通过功能使用dblink,从遥控潜水器中取出数据,但发现错误<代码>,结果数据没有目的地。我正在使用普采克语这样做。

<>功能>:>

CREATE OR REPLACE FUNCTION fun()
  RETURNS text AS
$$
begin
select dblink_connect(
       port=5432 dbname=test user=postgres password=**** );

WITH a AS (
SELECT *
FROM dblink(
     SELECT slno,fname,mname,lname
    FROM    remote_tbl 
     ) AS t (slno int, fname text, mname text, lname text)
)
, b AS (
INSERT INTO temptab1
SELECT slno, name
FROM   a
)
, c AS (
INSERT INTO temptab2
SELECT slno, name
FROM   a
)
INSERT INTO temptab3
SELECT slno, name
FROM   a;


select dblink_disconnect();
end;
$$ 
LANGUAGE plpgsql;

www.un.org/Depts/DGACM/index_spanish.htm

SELECT fun();

ERROR: 查询没有结果数据目的地

Answer 1

储存的程序只是取回上次选举考试的结果。你们需要实际回报价值:

CREATE OR REPLACE FUNCTION fun() RETURNS text AS $$
BEGIN
    --- ....
    RETURN(SELECT dblink_disconnect());
END
$$ LANGUAGE plpgsql;

你重新犯了错误,因为邮政公司希望这一功能能够退回某种类型的文本,但你的职能并没有回报。

Answer 2

Use a plain SQL function instead of PL/PgSQL, or use SELECT INTO and ordinary RETURN.

Answer 3

你发现的错误的原因是,在<>和之间没有return,例如:

BEGIN update mytable set lastcodeused = to_char(cast(lastcodeused as INTEGER)+1, FM999999999999999999 ) where classid = classIdVar and appid= appIdInt RETURNING concat(pageUniqueCode,lastcodeused) as pageUniqueCode into taskcode; return taskcode; END;

Answer 4

If you have this error using a pgplsql procedure or function, and you are sure that the return is defined correctly, there exists a different non-intuitive cause. I needed some time to realize this so I think it is worth sharing. I had a function like this:

CREATE OR REPLACE FUNCTION "db".fn_x(
    id integer)
    RETURNS TABLE(b_val varchar(100), c_val varchar(100)) 
    LANGUAGE  plpgsql 

AS $BODY$
DECLARE 
var_b_val varchar(100);
var_c_val varchar(100);

BEGIN
    select var_b, var_c
        -- Missing INTO clause was the cause of the error.
        var_b_val, var_c_val
        from "db".table_y where y_id = id;

    return query(select var_b_val, var_c_val);
END;
$BODY$;

仅补充说,缺失的<代码>INTO条款使该职能正确运作。

最后,这一错误还可能引发无声的同义词错误。

Answer 5

我创建了一个 rel=“nofollow noreferer”>PL/pgSQL=,如下所示:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN
  SELECT 2;
END;
$$ LANGUAGE plpgsql;

然后,打电话my_func()的错误如下:

postgres=# SELECT my_func();
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 my_func() line 3 at SQL statement

因此,我替换了SlectT statement ,如下所示:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN
  -- SELECT 2;
  PERFORM 2;
END;
$$ LANGUAGE plpgsql;

然后,我可以打电话到<条码>my_func(,没有错误,如下所示:

postgres=# SELECT my_func();
 my_func
---------

(1 row)

并且,我设立了一个PL/pgSQL职能,具体如下:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
BEGIN
SELECT 2;
END;
$$ LANGUAGE plpgsql;

然后,打电话my_func()的错误如下:

postgres=# SELECT my_func();
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 my_func() line 3 at SQL statement

因此,我用替换了<代码>。

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
BEGIN
-- SELECT 2;
RETURN 2; -- Here
END;
$$ LANGUAGE plpgsql;

或,我将<代码>SlectT的说明载于RETURN说明如下:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
BEGIN
RETURN (SELECT 2); -- Here
END;
$$ LANGUAGE plpgsql;

然后,我可以打电话到<条码>my_func(,没有错误,如下所示:

postgres=# SELECT my_func();
 my_func
---------
       2
(1 row)

友情链接