Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save ivanlemeshev/e5a230b7eec1371054ce1c047841558b to your computer and use it in GitHub Desktop.
Save ivanlemeshev/e5a230b7eec1371054ce1c047841558b to your computer and use it in GitHub Desktop.
First and last aggregate functions for Postgresql
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT $1;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.FIRST (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
SELECT $2;
$$;
-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment