Subtracting one row variable from another in Postgres cursor

Question

I am using PostgreSQL PostgreSQL 13.9 with PostGIS 3.1 extension to calculate vectors between a sample of points from a larger table.

The function works fine if I specify the individual field names using dot notation in the example below:

CREATE OR REPLACE FUNCTION get_vectors() RETURNS void
AS $$
DECLARE points CURSOR FOR SELECT st_x(geom) as x, st_y(geom) as y, st_z(geom) as z FROM test_table ORDER BY id LIMIT 3;  
    p1 test_vectors%ROWTYPE;
    p3 test_vectors%ROWTYPE; 
    v1 test_vectors%ROWTYPE; 
BEGIN 
OPEN points; 
FETCH FIRST FROM points INTO p1; 
FETCH NEXT FROM points INTO p3;
v1.x := p3.x - p1.x;
DELETE FROM test_vectors;
INSERT INTO test_vectors VALUES (v1.*);
CLOSE points; 
END; 
$$ LANGUAGE plpgsql;

SELECT get_vectors();

SELECT * FROM test_vectors ;

The test_vectors table looks like:

x	y	z
-0.0940000000409782	[null]	[null]

However if I replace the subtraction with v1 := p3 - p1; to operate at the row level, I get an error saying ERROR: operator does not exist: test_vectors - test_vectors.

What is confusing me is that when using window function like LEAD or LAG, subtraction of one row is allowed (e.g. https://dba.stackexchange.com/questions/300023/postgresql-subtract-to-each-row-the-previous-row). Why doesn't this work inside a function with a declared ROWTYPE ?

Answer 1

In the example you linked lag() and lead() get a specific numeric-type field from another row, not the entire other row. That's what enables the subtraction directly on the output of those. In the documentation, you can see that selecting the value you want to get from the other row is not optional (unlike the stuff square brackets):

lead ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible

Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of a type compatible with value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.

In that case, value retrieved from the other row is of a built-in numeric type known to PostgreSQL and it has a built-in function that defines how to handle subtraction for that type.

In your case, the test_vectors is your own, custom, user-defined table, so there's no way for PostgreSQL to know what it would mean to subtract its whole rows, unless you define a function accepting records of this table, then a subtraction operator - that applies that function to its left and right argument when it detects they are both of test_vectors type.

You can also start with defining a custom type then create table your_table of your_custom_type; and so on.