plprql

pig build pkg plprql         # build RPM / DEB packages

pig repo add pgsql -u          # Add repo and update cache

CREATE EXTENSION plprql;

CREATE EXTENSION plprql;

CREATE FUNCTION match_stats(int)
RETURNS TABLE(player text, kd_ratio float) AS $$
  from matches
  filter match_id == $1
  group player (
    aggregate {
      total_kills = sum kills,
      total_deaths = sum deaths
    }
  )
  filter total_deaths > 0
  derive kd_ratio = total_kills / total_deaths
  select { player, kd_ratio }
$$ LANGUAGE plprql;

SELECT * FROM match_stats(42);

SELECT prql('from matches | filter player == "Player1"')
  AS (id int, match_id int, player text, kills int)
  LIMIT 2;

SELECT prql('from matches | filter player == "Player1"', 'cursor_name');
FETCH 2 FROM cursor_name;

SELECT prql_to_sql('from matches | filter player == "Player1"');

from employees                    # data source
filter department == "Engineering" # row filtering
derive monthly_salary = salary / 12 # computed columns
sort {-monthly_salary}            # sorting (- for descending)
select {name, monthly_salary}     # column projection
take 10                           # limit rows