How to implement the following query in postgresql?

Question

PostgreSQL

How to implement the following query working in mysql on postgresql?

SELECT `ID_1`, @last := `ID_2` AS `ID_2`
  FROM (
    SELECT `ID_1`, `ID_2` 
      FROM `table`
      ORDER BY `ID_1`, `ID_2`
  ) AS `t`, (SELECT @last:=0) as init
  WHERE `ID_1` > @last;

How would such variables look like in a postgresql query?

Answer 1

PostgreSQL cannot combine procedural (use of user variables) and declarative programming style in one query. To get a specific selection, you need to write a separate stored function:

CREATE OR REPLACE FUNCTION public.get_id1_id2() RETURNS SETOF test_table AS
$BODY$
DECLARE
 last integer := 0; -- аналог "SELECT @last:=0"
 r test_table%rowtype; -- курсор для выборки и анализа строки
BEGIN
  FOR r IN SELECT * FROM test_table -- проход по всей исходной выборки
  LOOP
    IF r."ID_1" > last THEN -- аналог "WHERE `ID_1` > @last;"
      last = r."ID_2"; -- аналог "@last := `ID_2`"
      RETURN NEXT r; -- включение текущей строки в выходную выборку
    END IF;
  END LOOP;
  RETURN;
END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Or implement post-processing of the full selection already in the application, for example, the implementation in PHP:

function get_id1_id2($rows)
{
  $out = []; // выходная выборка
  $last = 0; // аналог "SELECT @last:=0"
  foreach($rows as $row) // проход по всей исходной выборки
    if($row["ID_1"] > $last) // аналог "WHERE `ID_1` > @last;"
    {
      $last = $row["ID_2"]; // аналог "@last := `ID_2`"
      $out[] = $row; // включение текущей строки в выходную выборку
    }
  return $out;
}