postgresql 在 PL/pgSQL 中声明行类型变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32913214/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declare row type variable in PL/pgSQL
提问by Vyacheslav
As I found SELECT * FROM t INTO my_data;works only if:
我发现SELECT * FROM t INTO my_data;只有在以下情况下才有效：
DO $$
DECLARE
my_data t%ROWTYPE;
BEGIN
SELECT * FROM t INTO my_data WHERE id = ?;
END $$;
Am I right?
我对吗？
If I want to get only 2-3 columns instead of all columns. How can I define my_data?
如果我只想获得 2-3 列而不是所有列。我该如何定义my_data？
That is,
那是，
DO $$
DECLARE
my_data <WHAT HERE??>;
BEGIN
SELECT id,name,surname FROM t INTO my_data WHERE id = ?;
END $$;
回答by Erwin Brandstetter
get only 2-3 columns instead of all columns
仅获取 2-3 列而不是所有列
One way: use a recordvariable:
一种方法：使用record变量：
DO $$
DECLARE
_rec record;
BEGIN
SELECT INTO _rec
id, name, surname FROM t WHERE id = ?;
END $$;
Note that the structure of a recordtype is undefined until assigned. So you cannot reference columns (fields) before you do that.
请注意，record类型的结构在分配之前是未定义的。所以在你这样做之前你不能引用列（字段）。
Another way: assign multiple scalar variables:
另一种方式：分配多个标量变量：
DO $$
DECLARE
_id int;
_name text;
_surname text;
BEGIN
SELECT INTO _id, _name, _surname
id, name, surname FROM t WHERE id = ?;
END $$;
As for your first example: %ROWTYPEis just noise in Postgres. The documentation:
至于你的第一个例子：%ROWTYPE只是 Postgres 中的噪音。文档：
(Since every table has an associated composite type of the same name, it actually does not matter in PostgreSQL whether you write
%ROWTYPEor not. But the form with%ROWTYPEis more portable.)
（因为每张表都有一个关联的同名复合类型，在PostgreSQL中其实写
%ROWTYPE不写都无所谓，但是with的形式%ROWTYPE更便于移植。）
So:
所以：
DO $$
DECLARE
my_data t; -- table name serves as type name, too.
BEGIN
SELECT INTO my_data * FROM t WHERE id = ?;
END $$;
