使用％TYPE在PostgreSQL中声明复合类型的变量 [英] Declare variable of composite type in PostgreSQL using %TYPE

问题描述

问题:如何在存储的函数中将相同类型的变量声明为参数?

Question: How can I declare a variable of the same type a parameter in a stored function?

简单的答案是使用%TYPE，这有效:

The simple answer is use %TYPE, this works:

CREATE OR REPLACE FUNCTION test_function_1(param1 text)
  RETURNS integer AS
$BODY$ 
DECLARE
    myVariable param1%TYPE;
BEGIN
    return 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

但是问题是param1是复合类型时

But the problem is when param1 is a composite type:

CREATE TYPE comp_type as
(
    field1 text
)

CREATE OR REPLACE FUNCTION test_function_2(param1 comp_type)
  RETURNS integer AS
$BODY$ 
DECLARE
    myVariable param1%TYPE;
BEGIN
    return 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

这不起作用:

ERROR: type comp_type does not exist [SQL State=42704]

param1是复合类型时该怎么办?

So how can I do when param1 is a composite type?

(注意:myVariable comp_type并不是一个好选择，因为我的功能稍微复杂些.)

(Note: Just myVariable comp_type is not a good option because my function is slightly more complex.)

我在复制粘贴时出错，真正的错误是:

Edited: I had a mistake on copy&paste, the real error is:

ERROR: invalid type name "param1%TYPE"
  Position: 130 [SQL State=42601] 

使用param1%ROWTYPE时，错误是:

ERROR: relation "param1" does not exist
  Where: compilation of PL/pgSQL function "test_function_2" near line 3 [SQL State=42P01] 

推荐答案

使用 %ROWTYPE .

A.H.和DavidEG进行的测试表明这行不通.有趣的问题！
您可以尝试解决方法.只要您的定义像示例一样，您就可以简单地诉诸于

Tests by A.H. and DavidEG have shown this won't work. Interesting problem!
You could try a workaround. As long as your definition is like the example you can simply resort to

CREATE FUNCTION test(param1 comp_type)
  RETURNS integer AS
$BODY$ 
DECLARE
    myvar comp_type;
BEGIN
    return 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

但是您真正的问题可能不是那么简单吗?

But your real problem is probably not as simple as that?

正如预期的那样，真正的问题更加复杂:多态输入类型.
在这种情况下，解决方法要难一些，但应该可以正常使用:

As expected, the real problem is more complex: a polymorphic input type.
Workaround for that scenario was harder, but should work flawlessly:

CREATE FUNCTION test(param1 anyelement, OUT a integer, OUT myvar anyelement)
  RETURNS record AS
$BODY$
BEGIN
    myvar := $1;  -- myvar has now the required type.

    --- do stuff with myvar.

    myvar := NULL;  -- reset if you don't want to output ..
    a := 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

致电:

SELECT a FROM test('("foo")'::comp_type); -- just retrieve a, ignore myvar

查看完整的输出:

SELECT * FROM test('("foo")'::comp_type);

对于PostgreSQL 9.0+的注释

v9.0中进行了重要更新.我引用了发行说明:

• 允许在PL/pgSQL函数中为输入参数分配值 (史蒂夫·普伦蒂斯)

• Allow input parameters to be assigned values within PL/pgSQL functions (Steve Prentice)

以前，输入参数被视为声明为CONST，因此 该函数的代码无法更改其值.这个限制 已删除，以简化从其他DBMS的功能移植 没有施加相同的限制.现在输入参数 就像初始化为传入值的本地变量一样.

Formerly, input parameters were treated as being declared CONST, so the function's code could not change their values. This restriction has been removed to simplify porting of functions from other DBMSes that do not impose the equivalent restriction. An input parameter now acts like a local variable initialized to the passed-in value.

Ergo，除了我的解决方法外，您还可以直接利用输入变量.

Ergo, in addition to my workaround, you can utilize input variables directly.

• 如何在PostgreSQL中克隆RECORD
• 如何设置复合变量的值动态SQL字段

• How to clone a RECORD in PostgreSQL
• How to set value of composite variable field using dynamic SQL

这篇关于使用％TYPE在PostgreSQL中声明复合类型的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！