Inquiry about e-mail sending through pgAgent jobs

[ghe]

Good day to all,

 

I just want to ask if anyone here know about how can i send email through pgAgent jobs?

 

As far as I know, there is no way i can send an email via PostgreSQL, so what i will do is, i will send e-mail using PHP, but then, my problem is, i want my email sending operation to be scheduled. So i need to use pgAgent jobs for the scheduling of e-mail sending operation.

 

So, is there a way that i can call in pgAgent or PostgreSQL the function (created in PHP) that sends an e-mail ?

 

Thank you and regards,

[POSTGRES_GOD]

This script set handles email, error control, automatic email notification and exception handling.

Requires PLTCLU and a little bit of configuration.

 

Using PGAGENT, call the function to send mail included.  It is highly recommended you install this on a clean empty database to better look through and understand what is being accomplished and the multitude of opportunities this will open up for you.

 

CREATE LANGUAGE pltclu;

  HANDLER pltclu_call_handler;

 

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'

  HANDLER plpgsql_call_handler

  VALIDATOR plpgsql_validator;

 

CREATE AGGREGATE postgres.key_values (

  sfunc = array_cat,

  basetype = numeric[],

  stype = numeric[],

  initcond = '{}'

);

 

CREATE AGGREGATE postgres.smallint_values (

  sfunc = array_cat,

  basetype = SMALLINT[],

  stype = SMALLINT[],

  initcond = '{}'

);

 

CREATE AGGREGATE postgres.string_array (

  sfunc = array_cat,

  basetype = TEXT[],

  stype = TEXT[],

  initcond = '{}'

);

 

CREATE OR REPLACE FUNCTION postgres.text_cat(i_text1 TEXT, i_text2 TEXT)

  RETURNS TEXT AS

$BODY$

BEGIN

  RETURN COALESCE(i_text1, '') || COALESCE(i_text2, '');

END;

$BODY$

LANGUAGE 'plpgsql' STRICT VOLATILE;

 

CREATE AGGREGATE postgres.strings (

  sfunc = text_cat,

  basetype = TEXT,

  stype = TEXT,

  initcond = ''

);

 

 

 

CREATE SCHEMA rprts;

 

 

ALTER SCHEMA rprts OWNER TO postgres;

 

CREATE OR REPLACE FUNCTION rprts.pgmail(text, text, text, text, text, text)

  RETURNS integer AS

$BODY$

          set mailfrom      $1

          set mailto        $2

          set mailcc        $3

          set mailserver    $4

          set mailsubject  $5

          set mailmessage  $6

 

# Mail Server

          set myHost $mailserver

 

# Mail Server SMTP Port   

          set myPort 25

          set mySock [socket $myHost $myPort]

 

# Mail To Address

          set toemailaddress_start [string first "<" $mailto]

 

          if {$toemailaddress_start != -1} {

                  set toemailaddress_finish [string first ">" $mailto]

                  set toemailaddress_start [expr $toemailaddress_start + 1]

                  set toemailaddress_finish [expr $toemailaddress_finish - 1]

                  set toemailaddress [string range $mailto $toemailaddress_start $toemailaddress_finish]

          } else {

                  set toemailaddress $mailto

          }

 

# Mail Cc Address

 

          set ccemailaddress_start [string first "<" $mailcc]

          if {$ccemailaddress_start != -1} {

                  set ccemailaddress_finish [string first ">" $mailcc]

                  set ccemailaddress_start [expr $ccemailaddress_start + 1]

                  set ccemailaddress_finish [expr $ccemailaddress_finish - 1]

                  set ccemailaddress [string range $mailcc $toemailaddress_start $ccemailaddress_finish]

          } else {

                  set ccemailaddress $mailcc

          }

 

# Mail From Address

          set fromemailaddress_start [string first "<" $mailfrom]

          if {$fromemailaddress_start != -1} {

                  set fromemailaddress_finish [string first ">" $mailfrom]

                  set fromemailaddress_start [expr $fromemailaddress_start + 1]

                  set fromemailaddress_finish [expr $fromemailaddress_finish - 1]

                  set fromemailaddress [string range $mailfrom $fromemailaddress_start $fromemailaddress_finish]

          } else {

                  set fromemailaddress $mailfrom

          }

          fileevent $mySock writable

                  fconfigure $mySock -buffering none

                  puts $mySock "helo $mailserver"

                  gets $mySock name

                  puts $mySock "mail from: $fromemailaddress"

                  gets $mySock name

                  puts $mySock "rcpt to: $toemailaddress"

                  gets $mySock name

                  puts $mySock "rcpt cc: $ccemailaddress"

                  gets $mySock name

                  puts $mySock "data"

                  gets $mySock name

                  puts $mySock "To: $mailto"

                  puts $mySock "Cc: $mailcc"

                  puts $mySock "From: $mailfrom"

                  puts $mySock "Subject: $mailsubject"

                  puts $mySock ""

                  puts $mySock "$mailmessage"

                  puts $mySock "."

                  gets $mySock name

 

          close $mySock

 

return 1$BODY$

  LANGUAGE 'pltclu' VOLATILE;

ALTER FUNCTION rprts.pgmail(text, text, text, text, text, text) OWNER TO postgres;

 

CREATE FUNCTION rprts.checkerrmail() RETURNS "trigger"

    AS $$

DECLARE

customerRec RECORD;

textMessage TEXT;

        rec        RECORD;

        dbx        TEXT;

BEGIN

select into customerRec * from rprts.px_fails order by fail_id desc limit 1;

if customerRec.prrty = 1 then

for rec in select full_name||'<'||eml_address||'>' as recep,(SELECT view_catalog from information_schema.view_table_usage limit 1) as db_core, full_name from rprts.eml where role_id = 1 loop

textMessage := 'Greetings '||rec.full_name||'.

 

  A "Critical" error with the potential of a catastrophic event has occured on server '||host(inet_server_addr())::text||'. The IP address of the user who was connected is '||host(inet_client_addr())::text||'

and they were logged in as '||"session_user"()||'.

 

The details of the error are included below:

 

--'||customerRec.flr_nfo||'

 

 

Sent '||now()::text||' by the database critical notification process...

 

 

------------------------------------

 

Please do not reply to this automatically system-generated email.

You will not recieve a response.

 

 

    Confidentiality Notice:

 

This e-mail contains proprietary information some of regarding Zonar Systems which may be legally privileged. It is for the intended recipient(s) only. If you believe that it has been sent to you in error, please notify the sender by reply e-mail and delete the original message. Any disclosure, copying, distribution or use of this information by someone other than the intended recipient(s) is prohibited and may be unlawful.

 

 

'::text;

                dbx := 'DB Warning System for database('||rec.db_core||') on host '||host(inet_server_addr())::text||'<'||host(inet_server_addr())::text||'@zonarsystems.com>'::text;

perform rprts.pgmail(dbx, rec.recep, '', 'mail.zonarsystems.com', 'Critical Database Warning', textMessage);

        end loop;

end if;

return NEW;

END;$$

    LANGUAGE plpgsql;

 

 

ALTER FUNCTION rprts.checkerrmail() OWNER TO postgres;

 

 

CREATE FUNCTION rprts.err_log() RETURNS void

    AS $$

DECLARE

customerRec RECORD;

textMessage TEXT;

        rec        RECORD;

        dbx        TEXT;

        dbc        INTEGER := (select count(*) from rprts.px_fails where rslvd = 'false')::integer;

BEGIN

 

IF dbc > 0 then

for rec in select full_name||'<'||eml_address||'>' as recep,(SELECT view_catalog from information_schema.view_table_usage limit 1) as db_core, full_name from rprts.eml where role_id in(1,2,3) loop

textMessage := 'Greetings '||rec.full_name||'.

 

  A non-critical error event report has been generated for server '||host(inet_server_addr())::text||'.

 

The details of the error report are included below:

 

'||(select strings(xk) from (select '--'||flr_nfo||' on '||tstmp::text||'

 

' as xk from rprts.px_fails where rslvd = 'false')x1)||'

 

 

Sent '||now()::text||' by the database critical notification process...

 

 

------------------------------------

 

Please do not reply to this automatically system-generated email.

You will not recieve a response.

 

 

    Confidentiality Notice:

 

This e-mail contains proprietary information some of regarding *** which may be legally privileged. It is for the intended recipient(s) only. If you believe that it has been sent to you in error, please notify the sender by reply e-mail and delete the original message. Any disclosure, copying, distribution or use of this information by someone other than the intended recipient(s) is prohibited and may be unlawful.

 

 

'::text;

                dbx := 'DB Warning System for database('||rec.db_core||') on host '||host(inet_server_addr())::text||'<'||host(inet_server_addr())::text||'@yourdomain.com>'::text;

perform rprts.pgmail(dbx, rec.recep, '', 'mail.yourdomain.com', 'Critical Database Warning', textMessage);

        end loop;

        update rprts.px_fails set rslvd = 'true';

end if;

END;$$

    LANGUAGE plpgsql;

 

 

ALTER FUNCTION rprts.err_log() OWNER TO postgres;

 

 

 

CREATE FUNCTION rprts.lg_exptn(i_ver_id bigint, i_err text, i_err_cde character varying, i_prrty integer) RETURNS void

    AS $$

DECLARE

BEGIN

INSERT INTO

rprts.px_fails

  (

    ver_id

  , flr_nfo

  , err_cde

  , prrty

  )

VALUES

  (

    i_ver_id

  , i_err

  , i_err_cde

  , i_prrty

  );

END;

$$

    LANGUAGE plpgsql;

 

 

ALTER FUNCTION rprts.lg_exptn(i_ver_id bigint, i_err text, i_err_cde character varying, i_prrty integer) OWNER TO postgres;

 

 

 

CREATE FUNCTION rprts.rscript() RETURNS void

    AS $$

declare

rnx text := ' ';

rec record;

err text;

begin

  for rec in SELECT lscript.sval

  FROM rprts.lscript loop

 

  rnx:=rnx||' '||rec.sval;

 

  end loop;

    EXECUTE rnx;

      Delete From rprts.lscript;

  EXCEPTION

  WHEN OTHERS THEN

 

err := 'Script '||(select sval from rprts.lscript limit 1) ||' failed with - '||SQLERRM;

 

perform rprts.lg_exptn(

 

  (select max(ver_id) as ver_id from rprts.px_ver)::bigint

, err::text

, SQLSTATE::character varying

, (select prrty from rprts.cde_rnk where err_cde = SQLSTATE)::integer

);

 

  Delete From rprts.lscript;

end;

 

$$

    LANGUAGE plpgsql;

 

 

ALTER FUNCTION rprts.rscript() OWNER TO postgres;

 

 

 

CREATE TABLE rprts.cde_rnk (

    err_cde character varying(5) NOT NULL,

    err_means character varying(150),

    prrty integer

);

 

 

ALTER TABLE rprts.cde_rnk OWNER TO postgres;

 

 

 

CREATE TABLE rprts.eml (

    eml_id bigint NOT NULL,

    full_name character varying DEFAULT 'Responsible Person'::character varying,

    eml_address character varying DEFAULT 'user@email.com'::character varying,

    role_id bigint DEFAULT 1

);

 

 

ALTER TABLE rprts.eml OWNER TO postgres;

 

 

 

COMMENT ON TABLE rprts.eml IS 'email list';

 

 

 

COMMENT ON COLUMN eml.eml_id IS 'Primary key';

 

 

COMMENT ON COLUMN eml.eml_address IS 'email address';

 

 

COMMENT ON COLUMN eml.role_id IS 'Role type to send to';

 

 

CREATE SEQUENCE rprts.eml_eml_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MAXVALUE

    NO MINVALUE

    CACHE 1;

 

 

ALTER TABLE rprts.eml_eml_id_seq OWNER TO postgres;

 

 

ALTER SEQUENCE rprts.eml_eml_id_seq OWNED BY eml.eml_id;

 

 

CREATE TABLE rprts.lscript (

    sval text

);

 

 

ALTER TABLE rprts.lscript OWNER TO postgres;

 

 

CREATE TABLE rprts.px_fails (

    fail_id bigint NOT NULL,

    ver_id bigint,

    flr_nfo text,

    tstmp timestamp without time zone DEFAULT now(),

    err_cde character varying(5),

    prrty integer DEFAULT 5,

    rslvd boolean DEFAULT false NOT NULL

);

 

 

ALTER TABLE rprts.px_fails OWNER TO postgres;

 

 

 

CREATE SEQUENCE rprts.px_fails_fail_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MAXVALUE

    NO MINVALUE

    CACHE 1;

 

 

ALTER TABLE rprts.px_fails_fail_id_seq OWNER TO postgres;

 

 

 

ALTER SEQUENCE rprts.px_fails_fail_id_seq OWNED BY px_fails.fail_id;

 

 

 

 

CREATE TABLE rprts.px_ver (

    ver_id bigint NOT NULL,

    ver character varying(35),

    nme character varying(35),

    dte timestamp without time zone DEFAULT now(),

    stts character varying(10) DEFAULT 'in process'::character varying NOT NULL,

    CONSTRAINT stts_chk CHECK (((stts)::text = ANY ((ARRAY['passed'::character varying, 'in process'::character varying, 'failed'::character varying])::text[])))

);

 

 

ALTER TABLE rprts.px_ver OWNER TO postgres;

 

 

 

CREATE SEQUENCE rprts.px_ver_ver_id_seq

    START WITH 1

    INCREMENT BY 1

    NO MAXVALUE

    NO MINVALUE

    CACHE 1;

 

 

ALTER TABLE rprts.px_ver_ver_id_seq OWNER TO postgres;

 

 

 

ALTER SEQUENCE rprts.px_ver_ver_id_seq OWNED BY px_ver.ver_id;

 

 

 

 

CREATE TABLE rprts.tmp_val_1 (

    tmp_val_1 character varying

);

 

 

ALTER TABLE rprts.tmp_val_1 OWNER TO postgres;

 

 

 

CREATE TABLE rprts.tmp_val_2 (

    tmp_val_2 character varying

);

 

 

ALTER TABLE rprts.tmp_val_2 OWNER TO postgres;

 

 

 

ALTER TABLE rprts.eml ALTER COLUMN eml_id SET DEFAULT nextval('eml_eml_id_seq'::regclass);

 

 

 

 

ALTER TABLE rprts.px_fails ALTER COLUMN fail_id SET DEFAULT nextval('px_fails_fail_id_seq'::regclass);

 

 

 

ALTER TABLE rprts.px_ver ALTER COLUMN ver_id SET DEFAULT nextval('px_ver_ver_id_seq'::regclass);

 

 

 

 

ALTER TABLE ONLY rprts.cde_rnk

    ADD CONSTRAINT cde_rnk_pkey PRIMARY KEY (err_cde);

 

 

 

ALTER TABLE ONLY rprts.eml

    ADD CONSTRAINT eml_pkey PRIMARY KEY (eml_id);

 

 

 

 

ALTER TABLE ONLY rprts.px_fails

    ADD CONSTRAINT px_fails_pkey PRIMARY KEY (fail_id);

 

 

 

ALTER TABLE ONLY rprts.px_ver

    ADD CONSTRAINT px_ver_pkey PRIMARY KEY (ver_id);

 

 

 

 

CREATE TRIGGER rprts.trgcheckerrmail

    BEFORE INSERT ON px_fails

    FOR EACH STATEMENT

    EXECUTE PROCEDURE checkerrmail();

 

 

 

REVOKE ALL ON FUNCTION rprts.lg_exptn(i_ver_id bigint, i_err text, i_err_cde character varying, i_prrty integer) FROM PUBLIC;

REVOKE ALL ON FUNCTION rprts.lg_exptn(i_ver_id bigint, i_err text, i_err_cde character varying, i_prrty integer) FROM postgres;

GRANT ALL ON FUNCTION rprts.lg_exptn(i_ver_id bigint, i_err text, i_err_cde character varying, i_prrty integer) TO postgres;

GRANT ALL ON FUNCTION rprts.lg_exptn(i_ver_id bigint, i_err text, i_err_cde character varying, i_prrty integer) TO PUBLIC;

 

 

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('00000', 'SUCCESSFUL COMPLETION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01000', 'WARNING', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0100C', 'DYNAMIC RESULT SETS RETURNED', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01008', 'IMPLICIT ZERO BIT PADDING', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01003', 'NULL VALUE ELIMINATED IN SET FUNCTION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01007', 'PRIVILEGE NOT GRANTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01006', 'PRIVILEGE NOT REVOKED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01004', 'STRING DATA RIGHT TRUNCATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('01P01', 'DEPRECATED FEATURE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('02000', 'NO DATA', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('02001', 'NO ADDITIONAL DYNAMIC RESULT SETS RETURNED', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('03000', 'SQL STATEMENT NOT YET COMPLETE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08000', 'CONNECTION EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08003', 'CONNECTION DOES NOT EXIST', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08006', 'CONNECTION FAILURE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08001', 'SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08004', 'SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08007', 'TRANSACTION RESOLUTION UNKNOWN', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('08P01', 'PROTOCOL VIOLATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('09000', 'TRIGGERED ACTION EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0A000', 'FEATURE NOT SUPPORTED', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0B000', 'INVALID TRANSACTION INITIATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0F000', 'LOCATOR EXCEPTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0F001', 'INVALID LOCATOR SPECIFICATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0L000', 'INVALID GRANTOR', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0LP01', 'INVALID GRANT OPERATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('0P000', 'INVALID ROLE SPECIFICATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('21000', 'CARDINALITY VIOLATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22000', 'DATA EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2202E', 'ARRAY SUBSCRIPT ERROR', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22021', 'CHARACTER NOT IN REPERTOIRE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22008', 'DATETIME FIELD OVERFLOW', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22012', 'DIVISION BY ZERO', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22005', 'ERROR IN ASSIGNMENT', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2200B', 'ESCAPE CHARACTER CONFLICT', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22022', 'INDICATOR OVERFLOW', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22015', 'INTERVAL FIELD OVERFLOW', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2201E', 'INVALID ARGUMENT FOR LOGARITHM', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2201F', 'INVALID ARGUMENT FOR POWER FUNCTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2201G', 'INVALID ARGUMENT FOR WIDTH BUCKET FUNCTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22018', 'INVALID CHARACTER VALUE FOR CAST', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22007', 'INVALID DATETIME FORMAT', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22019', 'INVALID ESCAPE CHARACTER', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2200D', 'INVALID ESCAPE OCTET', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22025', 'INVALID ESCAPE SEQUENCE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22P06', 'NONSTANDARD USE OF ESCAPE CHARACTER', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22010', 'INVALID INDICATOR PARAMETER VALUE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22020', 'INVALID LIMIT VALUE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22023', 'INVALID PARAMETER VALUE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2201B', 'INVALID REGULAR EXPRESSION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22009', 'INVALID TIME ZONE DISPLACEMENT VALUE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2200C', 'INVALID USE OF ESCAPE CHARACTER', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2200G', 'MOST SPECIFIC TYPE MISMATCH', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22004', 'NULL VALUE NOT ALLOWED', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22002', 'NULL VALUE NO INDICATOR PARAMETER', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22003', 'NUMERIC VALUE OUT OF RANGE', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22026', 'STRING DATA LENGTH MISMATCH', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22001', 'STRING DATA RIGHT TRUNCATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22011', 'SUBSTRING ERROR', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22027', 'TRIM ERROR', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22024', 'UNTERMINATED C STRING', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2200F', 'ZERO LENGTH CHARACTER STRING', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22P01', 'FLOATING POINT EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22P02', 'INVALID TEXT REPRESENTATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22P03', 'INVALID BINARY REPRESENTATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22P04', 'BAD COPY FILE FORMAT', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('22P05', 'UNTRANSLATABLE CHARACTER', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('23000', 'INTEGRITY CONSTRAINT VIOLATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('23001', 'RESTRICT VIOLATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('23502', 'NOT NULL VIOLATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('23503', 'FOREIGN KEY VIOLATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('23505', 'UNIQUE VIOLATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('23514', 'CHECK VIOLATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('24000', 'INVALID CURSOR STATE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25000', 'INVALID TRANSACTION STATE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25001', 'ACTIVE SQL TRANSACTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25002', 'BRANCH TRANSACTION ALREADY ACTIVE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25008', 'HELD CURSOR REQUIRES SAME ISOLATION LEVEL', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25003', 'INAPPROPRIATE ACCESS MODE FOR BRANCH TRANSACTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25004', 'INAPPROPRIATE ISOLATION LEVEL FOR BRANCH TRANSACTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25005', 'NO ACTIVE SQL TRANSACTION FOR BRANCH TRANSACTION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25006', 'READ ONLY SQL TRANSACTION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25007', 'SCHEMA AND DATA STATEMENT MIXING NOT SUPPORTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25P01', 'NO ACTIVE SQL TRANSACTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('25P02', 'IN FAILED SQL TRANSACTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('26000', 'INVALID SQL STATEMENT NAME', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('27000', 'TRIGGERED DATA CHANGE VIOLATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('28000', 'INVALID AUTHORIZATION SPECIFICATION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2B000', 'DEPENDENT PRIVILEGE DESCRIPTORS STILL EXIST', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2BP01', 'DEPENDENT OBJECTS STILL EXIST', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2D000', 'INVALID TRANSACTION TERMINATION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2F000', 'SQL ROUTINE EXCEPTION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2F005', 'FUNCTION EXECUTED NO RETURN STATEMENT', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2F002', 'MODIFYING SQL DATA NOT PERMITTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2F003', 'PROHIBITED SQL STATEMENT ATTEMPTED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('2F004', 'READING SQL DATA NOT PERMITTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('34000', 'INVALID CURSOR NAME', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('38000', 'EXTERNAL ROUTINE EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('38001', 'CONTAINING SQL NOT PERMITTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('38002', 'MODIFYING SQL DATA NOT PERMITTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('38003', 'PROHIBITED SQL STATEMENT ATTEMPTED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('38004', 'READING SQL DATA NOT PERMITTED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('39000', 'EXTERNAL ROUTINE INVOCATION EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('39001', 'INVALID SQLSTATE RETURNED', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('39004', 'NULL VALUE NOT ALLOWED', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('39P01', 'TRIGGER PROTOCOL VIOLATED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('39P02', 'SRF PROTOCOL VIOLATED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('3B000', 'SAVEPOINT EXCEPTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('3B001', 'INVALID SAVEPOINT SPECIFICATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('3D000', 'INVALID CATALOG NAME', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('3F000', 'INVALID SCHEMA NAME', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('40000', 'TRANSACTION ROLLBACK', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('40002', 'TRANSACTION INTEGRITY CONSTRAINT VIOLATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('40001', 'SERIALIZATION FAILURE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('40003', 'STATEMENT COMPLETION UNKNOWN', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('40P01', 'DEADLOCK DETECTED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42000', 'SYNTAX ERROR OR ACCESS RULE VIOLATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42601', 'SYNTAX ERROR', 5);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42501', 'INSUFFICIENT PRIVILEGE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42846', 'CANNOT COERCE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42803', 'GROUPING ERROR', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42830', 'INVALID FOREIGN KEY', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42602', 'INVALID NAME', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42622', 'NAME TOO LONG', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42939', 'RESERVED NAME', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42804', 'DATATYPE MISMATCH', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P18', 'INDETERMINATE DATATYPE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42809', 'WRONG OBJECT TYPE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42703', 'UNDEFINED COLUMN', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42883', 'UNDEFINED FUNCTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P01', 'UNDEFINED TABLE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P02', 'UNDEFINED PARAMETER', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42704', 'UNDEFINED OBJECT', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42701', 'DUPLICATE COLUMN', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P03', 'DUPLICATE CURSOR', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P04', 'DUPLICATE DATABASE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42723', 'DUPLICATE FUNCTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P05', 'DUPLICATE PREPARED STATEMENT', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P06', 'DUPLICATE SCHEMA', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P07', 'DUPLICATE TABLE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42712', 'DUPLICATE ALIAS', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42710', 'DUPLICATE OBJECT', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42702', 'AMBIGUOUS COLUMN', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42725', 'AMBIGUOUS FUNCTION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P08', 'AMBIGUOUS PARAMETER', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P09', 'AMBIGUOUS ALIAS', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P10', 'INVALID COLUMN REFERENCE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42611', 'INVALID COLUMN DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P11', 'INVALID CURSOR DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P12', 'INVALID DATABASE DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P13', 'INVALID FUNCTION DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P14', 'INVALID PREPARED STATEMENT DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P15', 'INVALID SCHEMA DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P16', 'INVALID TABLE DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('42P17', 'INVALID OBJECT DEFINITION', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('44000', 'WITH CHECK OPTION VIOLATION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('53000', 'INSUFFICIENT RESOURCES', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('53100', 'DISK FULL', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('53200', 'OUT OF MEMORY', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('53300', 'TOO MANY CONNECTIONS', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('54000', 'PROGRAM LIMIT EXCEEDED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('54001', 'STATEMENT TOO COMPLEX', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('54011', 'TOO MANY COLUMNS', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('54023', 'TOO MANY ARGUMENTS', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('55000', 'OBJECT NOT IN PREREQUISITE STATE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('55006', 'OBJECT IN USE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('55P02', 'CANT CHANGE RUNTIME PARAM', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('55P03', 'LOCK NOT AVAILABLE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('57000', 'OPERATOR INTERVENTION', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('57014', 'QUERY CANCELED', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('57P01', 'ADMIN SHUTDOWN', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('57P02', 'CRASH SHUTDOWN', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('57P03', 'CANNOT CONNECT NOW', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('58030', 'IO ERROR', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('58P01', 'UNDEFINED FILE', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('58P02', 'DUPLICATE FILE', 2);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('F0000', 'CONFIG FILE ERROR', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('F0001', 'LOCK FILE EXISTS', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('P0000', 'PLPGSQL ERROR', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('P0001', 'RAISE EXCEPTION', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('P0002', 'NO DATA FOUND', 3);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('P0003', 'TOO MANY ROWS', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('XX000', 'INTERNAL ERROR', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('XX001', 'DATA CORRUPTED', 1);

INSERT INTO rprts.cde_rnk (err_cde, err_means, prrty) VALUES ('XX002', 'INDEX CORRUPTED', 1);