Differences between ROWTYPE, TYPE, and RECORD in postgresql?
Differences between ROWTYPE, TYPE, and RECORD in postgresql?
公众号guangcity
发布于 2022-12-02 20:36:21
发布于 2022-12-02 20:36:21
文章被收录于专栏：光城(guangcity)
How to understand differences between ROWTYPE, TYPE, and RECORD in postgresql?
Official documentation explains：https://www.postgresql.org/docs/9.1/plpgsql-declarations.html
- TYPE provides the data type of a variable or table column. You can use this to declare variables that will hold database values. For example, let's say you have a column named user_id in your users table. To declare a variable with the same data type as users.user_id you write: user_id users.user_id%TYPE;.
- ROWTYPE: A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a SELECT or FOR query result, so long as that query's column set matches the declared type of the variable. The individual fields of the row value are accessed using the usual dot notation, for example rowvar.field.
- RECORD: Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. A consequence of this is that until a record variable is first assigned to, it has no substructure, and any attempt to access a field in it will draw a run-time error.
TYPE CASE
postgres=# do $$
declare
counter integer := 1;
first_name varchar(50) := 'John';
last_name first_name%type := 'Doe';
payment numeric(11,2) := 20.5;
begin
raise notice '% % % has been paid % USD',
counter,
first_name,
last_name,
payment;
end $$;
NOTICE: 1 John Doe has been paid 20.50 USD
DO
ROWTYPE CASE
postgres=# create table actor(actor_id int, fname varchar(20), lname varchar(20));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'actor_id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
postgres=# insert into actor values ( 1, 'join', 'mal');
INSERT 0 1
postgres=# select * from actor;
actor_id | fname | lname
----------+-------+-------
1 | join | mal
(1 row)
postgres=# do $$
declare
selected_actor actor%rowtype;
begin
select *
from actor
into selected_actor;
-- show the number of actor
raise notice 'The actor name is % %',
selected_actor.fname,
selected_actor.lname;
end $$;
NOTICE: The actor name is join mal
DO
RECORD TYPE CASE
postgres=# do $$
declare
r record;
begin
select *
from actor
into r;
-- show the number of actor
raise notice 'The actor name is % %',
r.fname,
r.lname;
end $$;
NOTICE: The actor name is join mal
DO
end~
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-08-17，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
