Part 22- Function PostgreSQL

Function is a set of SQL procedural commands like declarations, assignments, loops, flow-of-control etc. 

Function have return type and we can calculate & perform operation and return output  .

Ex: Write function for calculate dept wise employees 

create function get_emp_details (emp_id  varchar(10))
returns int
language plpgsql
as
$$
declare
emp_data  integer ;
begin
select count(*)
into emp_data
from emp_details
where dept=emp_id ;
return emp_data;
end ;
$$

Ex-2 Create function for find max salary department

create or replace function fn_dept_higest_salary(dept_name text)
returns int
language plpgsql
as $$
declare D_salary integer ;
begin
select max(salary) into D_salary from emp_details where dept=dept_name group by dept;
return D_salary;
end ;
$$

Call function :-   select fn_dept_higest_salary('SALES')
 

Drop function :-  drop function fn_dept_higest_salary

Q1:

CREATE OR REPLACE FUNCTION public._add_overview_constraint(
    ovschema name,    ovtable name,    ovcolumn name,   refschema name,    reftable name,    refcolumn name,    factor integer)
  RETURNS boolean AS
$BODY$
    DECLARE
        fqtn text;
        cn name;
        sql text;
    BEGIN
        fqtn := '';
        IF length($1) > 0 THEN
            fqtn := quote_ident($1) || '.';
        END IF;
        fqtn := fqtn || quote_ident($2);

        cn := 'enforce_overview_' || $3;

        sql := 'ALTER TABLE ' || fqtn
            || ' ADD CONSTRAINT ' || quote_ident(cn)
            || ' CHECK ( public._overview_constraint(' || quote_ident($3)
            || ',' || $7
            || ',' || quote_literal($4)
            || ',' || quote_literal($5)
            || ',' || quote_literal($6)
            || '))';

        RETURN  public._add_raster_constraint(cn, sql);
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;

ALTER FUNCTION public._add_overview_constraint(name, name, name, name, name, name, integer)
  OWNER TO postgres;

-- Function: public.updaterastersrid(name, name, integer)

Q2:

CREATE OR REPLACE FUNCTION public.updaterastersrid(
    table_name name,
    column_name name,
    new_srid integer)
  RETURNS boolean AS
$BODY$ SELECT  public._UpdateRasterSRID('', $1, $2, $3) $BODY$
  LANGUAGE sql VOLATILE STRICT
  COST 100;
ALTER FUNCTION public.updaterastersrid(name, name, integer)
  OWNER TO postgres;
COMMENT ON FUNCTION public.updaterastersrid(name, name, integer) IS 'args: table_name, column_name, new_srid - Change the SRID of all rasters in the user-specified column and table.';
 

Q3: Create user define function with return type table