I want to loop through a query, but also retain the actual record for the next loop, so I can compare two adjacent rows.
CREATE OR REPLACE FUNCTION public.test ()
RETURNS void AS
$body$
DECLARE
previous RECORD;
actual RECORD;
query TEXT;
isdistinct BOOLEAN;
tablename VARCHAR;
columnname VARCHAR;
firstrow BOOLEAN DEFAULT TRUE;
BEGIN
tablename = 'naplo.esemeny';
columnname = 'esemeny_id';
query = 'SELECT * FROM ' || tablename || ' LIMIT 2';
FOR actual IN EXECUTE query LOOP
--do stuff
--save previous record
IF NOT firstrow THEN
EXECUTE 'SELECT ($1).' || columnname || ' IS DISTINCT FROM ($2).' || columnname
INTO isdistinct USING previous, actual;
RAISE NOTICE 'previous: %', previous.esemeny_id;
RAISE NOTICE 'actual: %', actual.esemeny_id;
RAISE NOTICE 'isdistinct: %', isdistinct;
ELSE
firstrow = false;
END IF;
previous = actual;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
The table:
CREATE TABLE naplo.esemeny (
esemeny_id SERIAL,
felhasznalo_id VARCHAR DEFAULT "current_user"() NOT NULL,
kotesszam VARCHAR(10),
idegen_azonosito INTEGER,
esemenytipus_id VARCHAR(10),
letrehozva TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
szoveg VARCHAR,
munkalap_id VARCHAR(13),
ajanlat_id INTEGER,
CONSTRAINT esemeny_pkey PRIMARY KEY(esemeny_id),
CONSTRAINT esemeny_fk_esemenytipus FOREIGN KEY (esemenytipus_id)
REFERENCES naplo.esemenytipus(esemenytipus_id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
)
WITH (oids = true);
The code above doesn't work, the following error message is thrown:
ERROR: could not identify column "esemeny_id" in record data type
LINE 1: SELECT ($1).esemeny_id IS DISTINCT FROM ($2).esemeny_id
^
QUERY: SELECT ($1).esemeny_id IS DISTINCT FROM ($2).esemeny_id
CONTEXT: PL/pgSQL function "test" line 18 at EXECUTE statement
LOG: duration: 0.000 ms statement: SET DateStyle TO 'ISO'
What am I missing?
Disclaimer: I know the code doesn't make too much sense, I only created so I can demonstrate the problem.
This does not directly answer your question, and may be of no use at all, since you did not really describe your end goal.
If the end goal is to be able to compare the value of a column in the current row with the value of the same column in the previous row, then you might be much better off using a windowing query:
SELECT actual, previous
FROM (
SELECT mycolumn AS actual,
lag(mycolumn) OVER () AS previous
FROM mytable
ORDER BY somecriteria
) as q
WHERE previous IS NOT NULL
AND actual IS DISTINCT FROM previous
This example prints the rows where the current row is different from the previous row.
Note that I added an ORDER BY clause - it does not make sense to talk about "the previous row" without specifying ordering, otherwise you would get random results.
This is plain SQL, not PlPgSQL, but if you can wrap it in a function if you want to dynamically generate the query.