在PL/pgSQL中声明行类型变量 [英] Declare row type variable in PL/pgSQL

问题描述

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

As I found SELECT * FROM t INTO my_data; works only if:

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

我说得对吗?

如果我只想获得2-3列而不是所有列.如何定义my_data?

If I want to get only 2-3 columns instead of all columns. How can I define my_data?

也就是说，

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

推荐答案

仅获取2-3列，而不是所有列

get only 2-3 columns instead of all columns

一种方法:使用 record 变量:

One way: use a record variable:

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

请注意，record类型的结构在分配之前是不确定的.因此，在执行此操作之前，您不能引用列(字段).

Note that the structure of a record type is undefined until assigned. So you cannot reference columns (fields) before you do that.

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

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 $$;

---

第一个示例:%ROWTYPE只是Postgres中的噪音. 文档:

---

As for your first example: %ROWTYPE is just noise in Postgres. The documentation:

(由于每个表都有一个同名的关联复合类型， 在PostgreSQL中，实际上是否写%ROWTYPE都无关紧要 或不.但是%ROWTYPE的形式更便于移植.)

(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 $$;

这篇关于在PL/pgSQL中声明行类型变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！