Question

I am experiencing unexpected behaviour on Postgres 9.0.4 using pl/pgsql relating to selecting from a function that returns a ROWTYPE into a ROWTYPE variable from within another function. In the example below I:

Create a table, TESTTABLE and insert a row.
Create a function FN_TEST_GET_ROW that returns a row of ROWTYPE TESTTABLE based on selection of a single row from TESTTABLE
Create a test harness in the form of a function TESTX that calls FN_TEST_GET_ROW with ID=1
Call the test harness

The error shown below is returned unexpectedly ERROR: invalid input syntax for integer: "(1,Fred)"

我只想把价值(1,Fred)归还,如果我执行的话,那就会出现这种情况。

SELECT fn_test_get_row(1);

directly.

编制表格:

CREATE TABLE testtable
(
id INTEGER,
name VARCHAR(10)
);

增加数据:

INSERT INTO testtable (id, name) VALUES (1,  Fred );

建立职能:

CREATE OR REPLACE FUNCTION fn_test_get_row(a INTEGER)
RETURNS testtable AS $$
DECLARE
i_row testtable;
BEGIN

SELECT *
INTO   i_row
FROM testtable
WHERE id = a;

-- Success
RETURN i_row;

END;
$$ LANGUAGE plpgsql;

建立测试功能:

CREATE OR REPLACE FUNCTION testx()
RETURNS testtable AS $$
DECLARE
i_row testtable;
BEGIN

SELECT fn_test_get_row(1)
INTO   i_row;

-- Success
RETURN i_row;
END;    
$$ LANGUAGE plpgsql;

Execute the test function:

select testx();

送回:

ERROR:  invalid input syntax for integer: "(1,Fred)"
CONTEXT:  PL/pgSQL function "testx" line 8 at SQL statement

********** Error **********

ERROR: invalid input syntax for integer: "(1,Fred)"
SQL state: 22P02
Context: PL/pgSQL function "testx" line 8 at SQL statement

Answer 1

页: 1 页: 1 RETURNS RECORD or RETURNS SETOF。这里是你们的固定职能。我所做的是将<代码>测试x功能改为将fn_test_get_row(<>/code>改为表,并将结果类别fn_test_get_row()改为一组。



CREATE OR REPLACE FUNCTION fn_test_get_row(a INTEGER)
RETURNS SETOF testtable AS $$
DECLARE
    i_row testtable%ROWTYPE;
BEGIN
    SELECT INTO i_row * FROM testtable WHERE id = a;
    RETURN NEXT i_row;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION testx()
RETURNS SETOF testtable AS $$
DECLARE
    i_row testtable%ROWTYPE;
BEGIN
    SELECT INTO i_row * FROM fn_test_get_row(1) AS foo;
    RETURN NEXT i_row;
END;
$$ LANGUAGE plpgsql;


赋予:

# select testx();
  testx   
---------- 
 (1,Fred)
(1 row)

Answer 2

似乎还要做的是将测试中的选择改为:

SELECT (fn_test_get_row(1)).*
INTO   i_row;

如果你认为,除括号外,你选择了你记录类型的一栏,错误信息就有意义。然后,请将这一栏改为你结果的第一栏,导致你发出错误信息。

友情链接