Postgres isn’t alone in referring to rows as tuples; it’s a term from mathematics, specifically it’s used in relational algebra which technically underpins the relational model. But there are other reasons…
Postgres was (re)built from the ground up as an Object-Relational Database. So while the most common place people find tuples is when they’re rows in a table, or returned by a query, they’re more than just rows. When you define a table, you define a custom object type that you can use in later table definitions:
CREATE TABLE first_table ( a text,b text);
CREATE TABLE second_table ( c text,d first_table);
INSERT INTO second_table (c,d) VALUES('blah',('first a','first b'));
If we then select from second_table, we can see that column d looks a bit odd. Digging further, we can see that d is an object (or “row”) in it’s own right!
derryh=# select * from second_table;
c | d
------+-----------------------
blah | ("first a","first b")
(1 row)
derryh=# select (d).* from second_table;
a | b
---------+---------
first a | first b
(1 row)
Backing up, can we request the entire “row” from second table as a single object, rather than a row of objects? Yes, we can!
derryh=# select second_table from second_table;
second_table
(blah,"(""first a"",""first b"")")
(1 row)
Having established that a Postgres tuple is actually a fairly generic object type, it shouldn’t be surprising that tables are fairly generic too. A physical table is only one kind of “tuple source” that Postgres supports. So, lets look at set returning functions that can blur the line in what they return.
CREATE FUNCTION example_tuples()
RETURNS SETOF second_table
LANGUAGE plpgsql
AS $$
DECLARE
n integer;
BEGIN
FOR n IN SELECT generate_series(1,5) LOOP
RETURN NEXT ('A '||n,('B '||n,'C '||n))::second_table;
END LOOP;
RETURN;
END;
$$;
derryh=# select example_tuples();
example_tuples
("A 1","(""B 1"",""C 1"")")
("A 2","(""B 2"",""C 2"")")
("A 3","(""B 3"",""C 3"")")
("A 4","(""B 4"",""C 4"")")
("A 5","(""B 5"",""C 5"")")
(5 rows)
derryh=# select (example_tuples()).*;
c | d
-----+---------------
A 1 | ("B 1","C 1")
A 2 | ("B 2","C 2")
A 3 | ("B 3","C 3")
A 4 | ("B 4","C 4")
A 5 | ("B 5","C 5")
(5 rows)
derryh=# select (example_tuples()).d.*;
a | b
-----+-----
B 1 | C 1
B 2 | C 2
B 3 | C 3
B 4 | C 4
B 5 | C 5
(5 rows)
But functions can use tuples as variables too, not just base types:
derryh=# CREATE FUNCTION example_variables()
RETURNS SETOF text
LANGUAGE plpgsql
AS $$
DECLARE
r record;
a second_table;
b first_table;
BEGIN
FOR r IN SELECT * from second_table LOOP
a := r;
b := a.d;
RETURN NEXT b.a;
END LOOP;
RETURN;
END;
$$;
derryh=# select * from example_variables();
example_variables
first a
second a
(2 rows)
That our “rows” are now scalar object variables that we can access the members of, (and members of the members – it’s elephants all the way down) means we’re now fairly far from the original concept of a row.
But rows can also be extended, just like other objects. Postgres uses the term INHERITS, where Java uses “extends”:
CREATE TABLE base_example(a text, b text);
CREATE TABLE extended_example(c text) INHERITS(base_example);
insert into extended_example(a,b,c) values('a','b','c');
derryh=# select * from extended_example;
a | b | c
---+---+---
a | b | c
(1 row)
It also offers Runtime Type Polymorphism. Since Postgres knows that extended_example is a subclass of base_example, it can be safely cast:
derryh=# select extended_example::base_example from extended_example;
extended_example
(a,b)
(1 row)
We can also define custom operators. To demonstrate what I mean, I’ll implement PHP style loose and strict comparisons, testing whether a tuple is the same account, or exactly the same details. Hopefully the code is clear enough, but the results are at the end of the block. Note that the operators can make use of the Primary Key index, so such comparisons can be pushed down to the database rather than performed in the app.
CREATE TABLE acct(
acct_number integer,
version integer,
info text,
primary key(acct_number,version)
);
insert into acct select generate_series(1,1000000),1,'Dave';
CREATE FUNCTION acct_barred(a acct,b acct)
RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
RAISE EXCEPTION 'SQL operator not permitted for this type!';
RETURN NULL;
END;
$$;
CREATE OPERATOR = (
leftarg = acct,
rightarg = acct,
procedure = acct_barred
);
CREATE FUNCTION acct_compare_loose(acct,acct)
RETURNS boolean
LANGUAGE sql
AS $$
SELECT $1.acct_number = $2.acct_number;
$$
;
CREATE OPERATOR == (
leftarg = acct,
rightarg = acct,
procedure = acct_compare_loose
);
CREATE FUNCTION acct_compare_strict(acct,acct)
RETURNS boolean
LANGUAGE sql
AS $$
SELECT
$1.acct_number = $2.acct_number
AND $1.version = $2.version
AND $1.info = $2.info;
$$
;
CREATE OPERATOR === (
leftarg = acct,
rightarg = acct,
procedure = acct_compare_strict
);
So that gives us the following results:
derryh=# select (1,1,'Dave')::acct = (1,2,'Fred')::acct AS equal;
ERROR: SQL operator not permitted for this type!
CONTEXT: PL/pgSQL function acct_barred(acct,acct) line 3 at RAISE
Time: 0.715 ms
derryh=# select (1,1,'Dave')::acct == (1,2,'Fred')::acct AS equal;
equal
t
(1 row)
derryh=# select (1,1,'Dave')::acct === (1,2,'Fred')::acct AS equal;
equal
f
(1 row)
derryh=# explain select * from acct where acct == (123,1,'Dave')::acct;
QUERY PLAN
Index Scan using acct_pkey on acct (cost=0.42..8.44 rows=1 width=13)
Index Cond: (acct_number = 123)
(2 rows)
Time: 0.826 ms
derryh=# explain select * from acct where acct === (123,1,'Dave')::acct;
QUERY PLAN
Index Scan using acct_pkey on acct (cost=0.42..8.45 rows=1 width=13)
Index Cond: ((acct_number = 123) AND (version = 1))
Filter: (info = 'Dave'::text)
(3 rows)
So, while SQL is pretty horrible for writing Object Oriented code in, hopefully that shows why tuples are more than just rows.