Get all ‘childs’

select
	p_child.project_id
from
	im_projects p_parent,
	im_projects p_child
where
	p_child.tree_sortkey between p_parent.tree_sortkey and tree_right(p_parent.tree_sortkey)
	and p_parent.project_id = 16030;

Get top parent project

        select  main_p.project_id
        from    im_projects p,
                im_projects main_p
        where   p.project_id = :project_id and
                tree_ancestor_key(p.tree_sortkey, 1) = main_p.tree_sortkey

Formatting Date Fields

to_char(u.last_visit, 'YYYY-MM-DD HH:SS') as last_visit_formatted,

All dates are in general of type timestamp which allows the the following operations:
Check if date lies before or after a reference date:p.start_date < '2006-04-04'

Get value name for categories

        select
                im_category_from_id(t.target_language_id) as target_language,
                im_category_from_id(t.source_language_id) as source_language
        from
                im_trans_tasks t,
        where
                and t.project_id = 123;

coalesce

		select
			sum(coalesce(s.billable_units,0)) as task_sum,
			trim(both ' ' from to_char(t.task_sum, :'999999999999D')) as task_sum_
		from
			im_trans_tasks;

CASE

select
        p.project_name as sub_project_name,
        p.project_nr as sub_project_nr,
        p.project_type_id as sub_project_type_id,
        p.project_status_id as sub_project_status_id,
        tree_ancestor_key(p.tree_sortkey, 1) as main_project_sortkey,

        trunc((c.paid_amount *
          im_exchange_rate(c.effective_date::date, c.currency, 'EUR')) :: numeric
          , 2) as paid_amount_converted,
        CASE c.cost_type_id = 3718
                WHEN true THEN
                        TO_CHAR(trunc((c.amount * im_exchange_rate(c.effective_date::date, c.currency, 'EUR') * '90' / 100 ) :: numeric, 2),‘LFM99G999G990D00')
                ELSE
                        trunc((c.amount * im_exchange_rate(c.effective_date::date, c.currency, 'EUR')) :: numeric, 2)
        END as amount_converted,
        c.*
from
        im_costs c
        LEFT OUTER JOIN im_projects p ON (c.project_id = p.project_id)
where
        NOT (c.customer_id = 28148 AND c.cost_type_id = 3718)
        and c.cost_type_id in (3704, 3706, 3718, 3720, 3700)
        and c.effective_date::date >= to_date('2009-09-28', 'YYYY-MM-DD')
        and c.effective_date::date < to_date('2099-12-31', 'YYYY-MM-DD')
        and c.effective_date::date < to_date('2099-12-31', 'YYYY-MM-DD')
        and p.project_status_id in (76, 78, 10000132)

LOOPs

CREATE OR REPLACE FUNCTION im_workflow_NAME__cleanup(INTEGER, text, text)
  RETURNS INTEGER AS '
declare
        p_case_id               alias for $1;
        p_transition_key        alias for $2;
        p_custom_arg            alias for $3;
 
        v_task_id               integer;        v_case_id               integer;
        v_object_id             integer;        v_creation_user         integer;
        v_creation_ip           varchar;        v_journal_id            integer;
        v_transition_key        varchar;        v_workflow_key          varchar;
        r                       record;
 
begin
        v_user_id := p_custom_arg;
 
        -- Select out some frequently used variables of the environment
        select  c.object_id, c.workflow_key, task_id, c.case_id
        into    v_object_id, v_workflow_key, v_task_id, v_case_id
        from    wf_tasks t, wf_cases c
        where   c.case_id = p_case_id
                and t.case_id = c.case_id
                and t.workflow_key = c.workflow_key
                and t.transition_key = p_transition_key;
 
 
        FOR r IN select request_id from notification_requests where object_id = v_object_id and user_id = v_user_id
        LOOP
                select notification_request__delete(r.request_id);
                perform acs_object__delete(r.request_id);
        END LOOP;
 
end;' LANGUAGE 'plpgsql' VOLATILE;

Frame for disposable functions

CREATE OR REPLACE FUNCTION inline_0 ()
RETURNS INTEGER AS '
 
declare
        v_count                 integer;
begin
 
        select count(*) into v_count from im_categories
        where category_id = 5006 or category_id = 5007;
 
        IF      0 != v_count
        THEN
                RAISE NOTICE ''upgrade-4.0.1.0.0-4.0.1.0.1.sql failed - could not add categories'';
                return 0;
        END IF;
 
        PERFORM im_category_new(5006, ''Overtime'', ''Intranet Absence Type'');
        PERFORM im_category_new(5007, ''Reduction in Working Hours'', ''Intranet Absence Type'');
 
        return 1;
 
end;' LANGUAGE 'plpgsql';
 
SELECT inline_0 ();
DROP FUNCTION inline_0 ();

Using the $body$ tag

create or replace function im_name_from_user_id(int4, int4) returns varchar as $body$
        DECLARE
                v_user_id       alias for $1;
                v_name_order    alias for $2;
                v_full_name     varchar(8000);
        BEGIN
                IF 2 == v_name_order THEN
                        select  last_names || ' ' || first_name
                        into    v_full_name
                        from    persons
                        where person_id = v_user_id;
                ELSEIF 3 == v_name_order THEN
                        select  last_names || ', ' || first_name
                        into    v_full_name
                        from    persons
                        where person_id = v_user_id;
                ELSE
                        select  first_names || ' ' || last_name
                        into    v_full_name
                        from    persons
                        where person_id = v_user_id;
                END IF;
                return v_full_name;
        END;$body$ language 'plpgsql';

Check for empty string

        IF      v_type_category_type IS NULL or length(v_type_category_type) = 0
        THEN
                update acs_object_types set type_category_type = ''Intranet SLA Parameter Type'' where object_type = ''im_sla_parameter'';
        END IF;

Auto ID's

create sequence im_customer_project_type_seq;
create table im_customer_project_type (
        id                      integer,
        company_id              integer
                                references im_companies,
        project_type_id         integer not null,
        unique(company_id, project_type_id)
);
ALTER TABLE im_customer_project_type ALTER COLUMN id SET DEFAULT NEXTVAL('im_customer_project_type_seq');