create serial by year in postgresql

serial_anual_func.sql

CREATE OR REPLACE FUNCTION serial_year_func()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE
    current_year INT;
    table_name TEXT;
		id_field_name TEXT;
		ano_field_name TEXT;
		last_id INT;			
		_idIsNull boolean;
		_anoIsNull boolean;
		result RECORD;		
BEGIN
-- based in https://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7782641#7782641
-- https://gist.github.com/insinfo/55f14bc4fcdbf6995371876b8c314e13

-- for debug  https://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQL-Debugger.html 
-- CREATE extension pldbgapi;

    current_year := EXTRACT(YEAR FROM NOW());
    table_name := TG_ARGV[0];
		id_field_name := TG_ARGV[1];
		ano_field_name := TG_ARGV[2];
		result := NEW;
		
		-- verifica se esta sendo passado o id manualmente no insert statment
		execute 'select $1.'|| quote_ident(id_field_name) ||' is null' using NEW into _idIsNull;
		
		-- verifica se esta sendo passado o ano manualmente no insert statement	
		execute 'select $1.'|| quote_ident(ano_field_name) ||' is null' using NEW into _anoIsNull;
		
		IF _idIsNull THEN
			-- pega o ultimo registro cadastrado na tabela								
			 EXECUTE format('SELECT %I FROM %I WHERE %I = $1 ORDER BY %I DESC LIMIT 1', id_field_name, table_name,ano_field_name, id_field_name)
        INTO last_id
        USING current_year;
			
			IF last_id IS NULL THEN
				 last_id := 0;
			END IF;	
			-- set NEW.id		
			create temp TABLE IF NOT EXISTS aux as select NEW.*;
			execute 'update aux set ' || quote_ident(id_field_name) || ' = ' ||  last_id + 1;
			select into result * from aux;	
					
			-- with hstore  CREATE EXTENSION hstore;			
			--NEW := NEW #= hstore(id_field_name, (last_id + 1)::text) ;			
				
 			-- with json not work
 			--EXECUTE 'SELECT json_populate_record($1, json_build_object(' || quote_literal(id_field_name) || ', $2))'  INTO result USING NEW, last_id + 1;
 			--result2 := result;
			
		END IF;
		
		
		IF _anoIsNull THEN			
			-- set NEW.ano_exercicio
			create temp TABLE IF NOT EXISTS aux as select NEW.*;
			execute 'update aux set ' || quote_ident(ano_field_name) || ' = ' ||  current_year;
			select into result * from aux;		
			
			
			--NEW := NEW #= hstore(ano_field_name, current_year::text );
			--EXECUTE 'SELECT json_populate_record($1, json_build_object(' || quote_literal(ano_field_name) || ', $2))' INTO result USING NEW, current_year;
			
		END IF;
		
		DROP TABLE IF EXISTS  aux;
		
    RETURN result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

using json_populate_record is not working

The serial_anual_func function is a PostgreSQL trigger function designed to automatically assign a sequential ID and the current year to specified fields in a table when a new record is inserted. This function ensures that the ID and year fields are populated correctly if they are not manually provided during the insert.

CREATE OR REPLACE FUNCTION public.serial_year_func()
RETURNS trigger AS $BODY$
DECLARE
    current_year INT;
    table_name TEXT;
    id_field_name TEXT;
    ano_field_name TEXT;
    last_id INT;
    _idIsNull boolean;
    _anoIsNull boolean;
    result RECORD;
BEGIN
    current_year := EXTRACT(YEAR FROM NOW());
    table_name := TG_ARGV[0];
    id_field_name := TG_ARGV[1];
    ano_field_name := TG_ARGV[2];
    result := NEW;

    -- Check if ID is manually provided in the insert statement
    EXECUTE 'SELECT ($1.' || quote_ident(id_field_name) || ' IS NULL)' INTO _idIsNull USING NEW;

    -- Check if year is manually provided in the insert statement
    EXECUTE 'SELECT ($1.' || quote_ident(ano_field_name) || ' IS NULL)' INTO _anoIsNull USING NEW;

    IF _idIsNull THEN
        -- Get the last inserted ID for the current year
        EXECUTE format('SELECT %I FROM %I WHERE %I = $1 ORDER BY %I DESC LIMIT 1', 
                       id_field_name, table_name, ano_field_name, id_field_name)
        INTO last_id USING current_year;

        IF last_id IS NULL THEN
            last_id := 0;
        END IF;

        -- Set NEW.id_field_name to last_id + 1
        CREATE TEMP TABLE IF NOT EXISTS aux AS SELECT NEW.*;
        EXECUTE 'UPDATE aux SET ' || quote_ident(id_field_name) || ' = ' || (last_id + 1);
        SELECT * INTO result FROM aux;
    END IF;

    IF _anoIsNull THEN
        -- Set NEW.ano_field_name to current_year
        CREATE TEMP TABLE IF NOT EXISTS aux AS SELECT NEW.*;
        EXECUTE 'UPDATE aux SET ' || quote_ident(ano_field_name) || ' = ' || current_year;
        SELECT * INTO result FROM aux;
    END IF;

    DROP TABLE IF EXISTS aux;

    RETURN result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Explanation of the Function
Variable Initialization: Initializes variables to store current year, table name, ID field name, year field name, last ID, and boolean flags to check if fields are null.

Check for Null ID:

Executes a query to check if the ID is null in the NEW record.
If null, fetches the last inserted ID for the current year, increments it by 1, and assigns it to the ID field of NEW.
Check for Null Year:

Executes a query to check if the year is null in the NEW record.
If null, assigns the current year to the year field of NEW.
Temporary Table Usage:

Uses a temporary table aux to store and update the NEW record values for both ID and year fields.
Ensures that the updates are correctly applied to the NEW record by selecting the modified record back into result.
Return Updated Record: Returns the modified NEW record.

Usage
To create a trigger using this function, use the following command:

CREATE TRIGGER serial_year_trigger
BEFORE INSERT ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION public.serial_anual_func('your_table_name', 'id_field_name', 'year_field_name');

Replace your_table_name, id_field_name, and ano_field_name with the actual table and field names. This trigger will ensure that the id_field_name and ano_field_name are automatically populated if not provided during an insert.