如何在PostgreSQL中克隆一个RECORD

问题描述

我想遍历一个查询，但还要保留下一个循环的实际记录，因此我可以比较两个相邻的行.

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;

表格:

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'

我想念什么?

免责声明:我知道代码的意义不大，我只是创建了代码，所以我可以证明问题所在.

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.

请注意，我添加了ORDER BY子句-在不指定顺序的情况下谈论上一行"是没有意义的，否则您将获得随机结果.

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.

这是普通的SQL，不是PlPgSQL，但是如果要动态生成查询，可以将其包装在函数中.

This is plain SQL, not PlPgSQL, but if you can wrap it in a function if you want to dynamically generate the query.

这篇关于如何在PostgreSQL中克隆一个RECORD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！