postgresql - 在 PL/pgSQL 中声明行类型变量

Question

我发现 SELECT * FROM t INTO my_data; 仅在以下情况下有效:

DO $$
DECLARE
my_data t%ROWTYPE;
BEGIN
SELECT * FROM t INTO my_data WHERE id = ?;
END $$;

我说得对吗？

如果我只想获取 2-3 列而不是所有列。如何定义 my_data？

也就是说，

DO $$
DECLARE
my_data <WHAT HERE??>;
BEGIN
SELECT id,name,surname FROM t INTO my_data WHERE id = ?;
END $$;

Answer 1

get only 2-3 columns instead of all columns

一种方法:使用 record变量:

DO $$
DECLARE
   _rec record;
BEGIN
SELECT INTO _rec
            id, name, surname FROM t WHERE id = ?;
END $$;

请注意，record 类型的结构在分配之前是未定义的。所以你不能在你这样做之前引用列(字段)。

另一种方式:分配多个标量变量:

DO $$
DECLARE
   _id int;
   _name text;
   _surname text;
BEGIN
SELECT INTO _id, _name, _surname
             id,  name,  surname FROM t WHERE id = ?;
END $$;

---

至于你的第一个例子:%ROWTYPE 在 Postgres 中只是噪音。 The documentation :

(Since every table has an associated composite type of the same name, it actually does not matter in PostgreSQL whether you write %ROWTYPE or not. But the form with %ROWTYPE is more portable.)

所以:

DO $$
DECLARE
   my_data t;  -- table name serves as type name, too. 
BEGIN
   SELECT INTO my_data  * FROM t WHERE id = ?;
END $$;