[SQL] My Trigger is not working :(
I think i missed some basics ... ERROR: column "ad_table_id" does not exist Where: PL/pgSQL function "oms_changelog" line 21 at assignment -- trigger definition -- CREATE OR REPLACE FUNCTION oms_changelog() RETURNS trigger AS $BODY$ DECLARE int_AD_COLUMN_ID numeric(10,0); int_AD_TABLE_ID numeric(10,0); BEGIN int_AD_COLUMN_ID = 0; int_AD_TABLE_ID = 0; int_AD_TABLE_ID = NEW.AD_TABLE_ID; int_AD_COLUMN_ID = NEW.AD_COLUMN_ID; IF (int_AD_COLUMN_ID > 100) THEN NEW.ad_table_id = AD_TABLE_ID; NEW.ad_column_id = AD_COLUMN_ID; END IF; -- RAISE EXCEPTION '% cannot have a negative salary', NEW.empname; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; Table definition: CREATE TABLE ad_changelog ( ad_changelog_id numeric(10,0) NOT NULL, ad_session_id numeric(10,0) NOT NULL, ad_table_id numeric(10,0) NOT NULL, ad_column_id numeric(10,0) NOT NULL, ad_client_id numeric(10,0) NOT NULL, ad_org_id numeric(10,0) NOT NULL, isactive character(1) NOT NULL DEFAULT 'Y'::bpchar, created timestamp without time zone NOT NULL DEFAULT now(), createdby numeric(10,0) NOT NULL, updated timestamp without time zone NOT NULL DEFAULT now(), updatedby numeric(10,0) NOT NULL, record_id numeric(10,0) NOT NULL, oldvalue character varying(2000), newvalue character varying(2000), undo character(1), redo character(1), iscustomization character(1) NOT NULL DEFAULT 'N'::bpchar, trxname character varying(60), description character varying(255), eventchangelog character(1), CONSTRAINT ad_changelog_pkey PRIMARY KEY (ad_changelog_id, ad_session_id, ad_table_id, ad_column_id), CONSTRAINT adcolumn_adchangelog FOREIGN KEY (ad_column_id) REFERENCES ad_column (ad_column_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT adsession_adchangelog FOREIGN KEY (ad_session_id) REFERENCES ad_session (ad_session_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, CONSTRAINT adtable_adchangelog FOREIGN KEY (ad_table_id) REFERENCES ad_table (ad_table_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT ad_changelog_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])), CONSTRAINT ad_changelog_iscustomization_check CHECK (iscustomization = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])) ) WITH ( OIDS=FALSE ); ALTER TABLE ad_changelog OWNER TO adempiere; -- Index: ad_changelog_speed -- DROP INDEX ad_changelog_speed; CREATE INDEX ad_changelog_speed ON ad_changelog USING btree (ad_table_id, record_id); -- Trigger: oms_changelog on ad_changelog -- DROP TRIGGER oms_changelog ON ad_changelog; CREATE TRIGGER oms_changelog BEFORE INSERT ON ad_changelog FOR EACH ROW EXECUTE PROCEDURE oms_changelog();
Re: [SQL] My Trigger is not working :(
DECLARE section not contain variable AD_TABLE_ID 2011/9/9, Waqar Azeem : > I think i missed some basics ... > > > ERROR: column "ad_table_id" does not exist > Where: PL/pgSQL function "oms_changelog" line 21 at assignment > > -- > trigger definition > -- > > CREATE OR REPLACE FUNCTION oms_changelog() > RETURNS trigger AS > $BODY$ > DECLARE > int_AD_COLUMN_ID numeric(10,0); > int_AD_TABLE_ID numeric(10,0); > BEGIN > > int_AD_COLUMN_ID = 0; > int_AD_TABLE_ID = 0; > > int_AD_TABLE_ID = NEW.AD_TABLE_ID; > int_AD_COLUMN_ID = NEW.AD_COLUMN_ID; > > IF (int_AD_COLUMN_ID > 100) THEN > NEW.ad_table_id = AD_TABLE_ID; > NEW.ad_column_id = AD_COLUMN_ID; > END IF; > > -- RAISE EXCEPTION '% cannot have a negative salary', NEW.empname; > RETURN NEW; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > > > > > Table definition: > > > CREATE TABLE ad_changelog > ( > ad_changelog_id numeric(10,0) NOT NULL, > ad_session_id numeric(10,0) NOT NULL, > ad_table_id numeric(10,0) NOT NULL, > ad_column_id numeric(10,0) NOT NULL, > ad_client_id numeric(10,0) NOT NULL, > ad_org_id numeric(10,0) NOT NULL, > isactive character(1) NOT NULL DEFAULT 'Y'::bpchar, > created timestamp without time zone NOT NULL DEFAULT now(), > createdby numeric(10,0) NOT NULL, > updated timestamp without time zone NOT NULL DEFAULT now(), > updatedby numeric(10,0) NOT NULL, > record_id numeric(10,0) NOT NULL, > oldvalue character varying(2000), > newvalue character varying(2000), > undo character(1), > redo character(1), > iscustomization character(1) NOT NULL DEFAULT 'N'::bpchar, > trxname character varying(60), > description character varying(255), > eventchangelog character(1), > CONSTRAINT ad_changelog_pkey PRIMARY KEY (ad_changelog_id, ad_session_id, > ad_table_id, ad_column_id), > CONSTRAINT adcolumn_adchangelog FOREIGN KEY (ad_column_id) > REFERENCES ad_column (ad_column_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, > CONSTRAINT adsession_adchangelog FOREIGN KEY (ad_session_id) > REFERENCES ad_session (ad_session_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED, > CONSTRAINT adtable_adchangelog FOREIGN KEY (ad_table_id) > REFERENCES ad_table (ad_table_id) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, > CONSTRAINT ad_changelog_isactive_check CHECK (isactive = ANY > (ARRAY['Y'::bpchar, 'N'::bpchar])), > CONSTRAINT ad_changelog_iscustomization_check CHECK (iscustomization = ANY > (ARRAY['Y'::bpchar, 'N'::bpchar])) > ) > WITH ( > OIDS=FALSE > ); > ALTER TABLE ad_changelog OWNER TO adempiere; > > -- Index: ad_changelog_speed > > -- DROP INDEX ad_changelog_speed; > > CREATE INDEX ad_changelog_speed > ON ad_changelog > USING btree > (ad_table_id, record_id); > > > -- Trigger: oms_changelog on ad_changelog > > -- DROP TRIGGER oms_changelog ON ad_changelog; > > CREATE TRIGGER oms_changelog > BEFORE INSERT > ON ad_changelog > FOR EACH ROW > EXECUTE PROCEDURE oms_changelog(); > -- pasman -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] My Trigger is not working :(
Thanks... After putting some code here ... starts getting this error ... SQL statement "INSERT INTO ad_changelog( ad_changelog_id, ad_session_id, ad_table_id, ad_column_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, record_id, oldvalue, newvalue, undo, redo, iscustomization, trxname, description, eventchangelog) VALUES ( $1 +1, $2 , $3 , $4 , $5 , $6 , $7 , $8 , $9 , $10 , $11 , $12 , $13 , $14 , $15 , $16 , $17 , $18 , $19 , $20 )" PL/pgSQL function "oms_changelog" line 19 at SQL statement -- code -- I need to duplicating the record that is inserted with some minor change. I simple add in insert into (see below) int_AD_COLUMN_ID = 0; int_AD_TABLE_ID = 0; int_AD_TABLE_ID = NEW.AD_TABLE_ID; int_AD_COLUMN_ID = NEW.AD_COLUMN_ID; IF (NEW.ad_table_id=157) THEN SELECT ad_table_id, ad_column_id INTO int_AD_COLUMN_ID, int_AD_TABLE_ID FROM ad_column WHERE ad_table_id=131 AND columnname LIKE (SELECT c.columnname FROM ad_column c WHERE c.ad_column_id=int_AD_COLUMN_ID); elsif (NEW.ad_table_id=158) then SELECT ad_table_id, ad_column_id INTO int_AD_COLUMN_ID, int_AD_TABLE_ID FROM ad_column WHERE ad_table_id=132 AND columnname LIKE (SELECT c.columnname FROM ad_column c WHERE c.ad_column_id=int_AD_COLUMN_ID); elsif (NEW.ad_table_id=159) then SELECT ad_table_id, ad_column_id INTO int_AD_COLUMN_ID, int_AD_TABLE_ID FROM ad_column WHERE ad_table_id=133 AND columnname LIKE (SELECT c.columnname FROM ad_column c WHERE c.ad_column_id=int_AD_COLUMN_ID); end if; IF (int_AD_COLUMN_ID > 100) THEN INSERT INTO ad_changelog( ad_changelog_id, ad_session_id, ad_table_id, ad_column_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, record_id, oldvalue, newvalue, undo, redo, iscustomization, trxname, description, eventchangelog) VALUES ( NEW.ad_changelog_id+1, NEW.ad_session_id, int_AD_TABLE_ID, int_AD_COLUMN_ID, NEW.ad_client_id, NEW.ad_org_id, NEW.isactive, NEW.created, NEW.createdby, NEW.updated, NEW.updatedby, NEW.record_id, NEW.oldvalue, NEW.newvalue, NEW.undo, NEW.redo, NEW.iscustomization, NEW.trxname, NEW.description, NEW.eventchangelog); END IF; 2011/9/9 pasman pasmański > DECLARE section not contain variable AD_TABLE_ID > > 2011/9/9, Waqar Azeem : > > I think i missed some basics ... > > > > > > ERROR: column "ad_table_id" does not exist > > Where: PL/pgSQL function "oms_changelog" line 21 at assignment > > > > -- > > trigger definition > > -- > > > > CREATE OR REPLACE FUNCTION oms_changelog() > > RETURNS trigger AS > > $BODY$ > > DECLARE > > int_AD_COLUMN_ID numeric(10,0); > > int_AD_TABLE_ID numeric(10,0); > > BEGIN > > > > int_AD_COLUMN_ID = 0; > > int_AD_TABLE_ID = 0; > > > > int_AD_TABLE_ID = NEW.AD_TABLE_ID; > > int_AD_COLUMN_ID = NEW.AD_COLUMN_ID; > > > > IF (int_AD_COLUMN_ID > 100) THEN > > NEW.ad_table_id = AD_TABLE_ID; > > NEW.ad_column_id = AD_COLUMN_ID; > > END IF; > > > > -- RAISE EXCEPTION '% cannot have a negative salary', > NEW.empname; > > RETURN NEW; > > END; > > $BODY$ > > LANGUAGE 'plpgsql' VOLATILE > > COST 100; > > > > > > > > > > Table definition: > > > > > > CREATE TABLE ad_changelog > > ( > > ad_changelog_id numeric(10,0) NOT NULL, > > ad_session_id numeric(10,0) NOT NULL, > > ad_table_id numeric(10,0) NOT NULL, > > ad_column_id numeric(10,0) NOT NULL, > > ad_client_id numeric(10,0) NOT NULL, > > ad_org_id numeric(10,0) NOT NULL, > > isactive character(1) NOT NULL DEFAULT 'Y'::bpchar, > > created timestamp without time zone NOT NULL DEFAULT now(), > > createdby numeric(10,0) NOT NULL, > > updated timestamp without time zone NOT NULL DEFAULT now(), > > updatedby numeric(10,0) NOT NULL, > > record_id numeric(10,0) NOT NULL, > > oldvalue character varying(2000), > > newvalue character varying(2000), > > undo character(1), > > redo character(1), > > iscustomization character(1) NOT NULL DEFAULT 'N'::bpchar, > > trxname character varying(60), > > description character varying(255), > > eventchangelog character(1), > > CONSTRAINT ad_changelog_pkey PRIMARY KEY (ad_changelog_id, > ad_session_id, > > ad_table_id, ad_column_id), > > CONSTRAINT adcolumn_adchangelog FOREIGN KEY (ad_column_id) > > REFERENCES ad_column (ad_column_id) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY > DEFERRED, > > CONSTRAINT adsession_adchangelog FOREIGN KEY (ad_session_id) > > REFERENCES ad_session (ad_session_id) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY > DEFERRED, > > CONSTRAINT adtable_adchangelog FOREIGN KEY (ad_ta
[SQL] Re: how can I get the length of columns of a table by system tables/views
SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT pg_class.oid FROM pg_class INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) = 'public') WHERE pg_class.relname='tablename') AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum; -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-can-I-get-the-length-of-columns-of-a-table-by-system-tables-views-tp4780987p4786248.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: how can I get the length of columns of a table by system tables/views
Why not to use information schema: select column_name, character_maximum_length from information_schema.columns where table_name ='table name' 09.09.2011, 16:53, "shuaixf" : > SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) > FROM pg_catalog.pg_attribute a > WHERE a.attrelid = (SELECT pg_class.oid > FROM pg_class INNER JOIN pg_namespace > ON (pg_class.relnamespace = pg_namespace.oid > AND lower(pg_namespace.nspname) = 'public') > WHERE pg_class.relname='tablename') > AND a.attnum > 0 AND NOT a.attisdropped > ORDER BY a.attnum; > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/how-can-I-get-the-length-of-columns-of-a-table-by-system-tables-views-tp4780987p4786248.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Dynamic sql
Hi, this is my first on this list. I want to know the basics of dynamic sql especially in PostgreSQL. I was googling for a while but have no luck for getting a good described examples. Maybe someone here could give me some links to the materials from which you where studying this subject. Ok so to be more accurate I am interested in examples of especially PL/SQL (PostgreSQL) code which will demonstrate examples with description :) Best regards Gabe
