How to clone a RECORD in PostgreSQL

Question

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.

Linderman

It says that there is no such column in this data type. Is that true? Perhaps the column name is just id? – Carothers

The function header (incl. return type and language declaration) is missing. I have seen this a couple of times and really wonder what makes people think it would be wise (or even acceptable) to rip of this essential part of the function. Please edit your question. – Cabezon

The column name in question exists. – Linderman

Function header and table DDL added, thanks – Linderman

Answer 1

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.

Ionization

I have a base table, which contains the structure of the data. I have two descendants: the active and the archive data. Every time a row changes, a copy of the original is inserted into the archive (with timestamp and username). This way the base table has every version of its records (since it aggregates the data from the descendants). My goal is to write a function which takes the name of the relation and a key value, and then returns each change as a separate row: timestamp, user, affected column, old value and new value. Your idea is a good one, I'm gonna try to implement – Linderman

Answer 2

I am pretty sure, there is a better solution for your actual problem. But to answer the question asked, here is a solution with polymorphic types:

The main problem is that you need well known composite types to work with. the structure of anonymous records is undefined until assigned.

CREATE OR REPLACE FUNCTION public.test (actual anyelement, _col text
                                      , OUT previous anyelement) AS
$func$
DECLARE
   isdistinct bool;
BEGIN
   FOR actual IN
      EXECUTE format('SELECT * FROM %s LIMIT 3', pg_typeof(actual))
   LOOP
        EXECUTE format('SELECT ($1).%1$I IS DISTINCT FROM ($2).%1$I', _col)
        INTO   isdistinct
        USING  previous, actual;

      RAISE NOTICE 'previous: %; actual: %; isdistinct: %'
                  , previous, actual, isdistinct;

      previous := actual;
   END LOOP;

   previous := NULL;  -- reset dummy output (optional)
END
$func$ LANGUAGE plpgsql;

Call:

SELECT public.test(NULL::naplo.esemeny, 'esemeny_id')

I am abusing an OUT parameter, since it's not possible to declare additional variables with a polymorphic composite type (at least I have failed repeatedly).

If your column name is stable you can replace the second EXECUTE with a simple expression.

I am running out of time, explanation in these related answers:

• Declare variable of composite type in PostgreSQL using %TYPE
• Refactor a PL/pgSQL function to return the output of various SELECT queries

Asides:

• Don't quote the language name, it's an identifier, not a string.
• Do you really need WITH (oids = true) in your table? This is still allowed, but largely deprecated in modern Postgres.

Cabezon

This should work too, but I accepted the other answer as it was more elegant. The language name quoting and the WITH (oids = true) statement was auto-generated by a tool, it is not going to production. Thanks for pointing out, though! – Linderman

@belidzs: I added an answer to provide a solution for the actual question, which hits a weak spot of plpgsql. If you can solve your problem with a window function in plain SQL, for all means, do that. – Cabezon