Differences between ROWTYPE, TYPE, and RECORD in postgresql?

postgresql plpgsql rdbms sqldatatypes
mrg picture mrg · Jan 13, 2016 · Viewed 9k times · Source

What is the use of the following declarations, and where will we use these types of declarations?

myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

Answer

mech picture mech · Jan 13, 2016

From PostgreSQL's documentation:

  • 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.

See the link for more in-depth examples.

Related questions

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …

Read More
PostgreSQL IF statement

How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);

Read More
Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the …

Read More