Hi,
I am building a generic database in postgresql.
And I am having a lot of problem.... Yes, I am just studing and learning about
postgres.
Lets go:
My database have two schemas:1 - The MAIN schema2 - The System schema.
I created the system schema that contains the table_fields register for the Main
tables.
Ie:
...# createdb -U postgres test
...# createlang -d test -U postgres plpgsql
...# createlang -d test -U postgres pltcl
...# psql -U postgres test
CREATE SCHEMA main_system;CREATE SCHEMA main;
CREATE TABLE main_system.products_codes (field varchar(30) primary key, "check"
text);INSERT INTO main_system.products_codes VALUES ('internal',$$NOT EXISTS(select *
from main.products_codes where field_name='internal' and value='$value')$$);INSERT INTO main_system.products_codes
VALUES('barcode','true');
CREATE TABLE main.products (id serial primary key, description varchar(100),
field1 integer, fieldx varchar(10));INSERT INTO main.products(id,description) VALUES (1,'Computer product');
CREATE TABLE main.products_codes (id serial primary key, "references" integer
references MAIN.PRODUCTS, field_name varchar(30) references
MAIN_SYSTEM.PRODUCTS_CODES, "value" varchar(50));INSERT INTO main.products_codes("references",field_name,"value")
values
(1,'internal','COMPUTER-1');INSERT INTO main.products_codes("references",field_name,"value") values
(1,'barcode','1234567890123');
In my database I can create how much products codes fields I like just inserting
a record into MAIN_SYSTEM.PRODUCTS_CODES table.
Okay, then I created another function that cat the fields types for the main
table:
create or replace FUNCTION getfieldvalue(text,text,integer,varchar(30)) returns
text as$$ set schema_name $1; set table_name $2; set references_value $3; set field_value $4; set references_name
{"references"} set field_name {"field_name"} set point "." spi_exec "select value as getfieldvalue from
$schema_name$point$table_name
where $references_name = $references_value and $field_name = '$field_value'"; return $getfieldvalue; $$ language
'pltcl';
SELECT getfieldvalue('main','products_codes',1,'internal'); --Will return the
Internal product code for product id 1 SELECT getfieldvalue('main','products_codes',1,'barcode'); --Will return the
Barcode for product id 1
With this function I can view the "main_system fields", If in the future I would
need to create a new code field (ie), its so easy, just type:INSERT INTO main_system.products_codes values
('mynewcodefield','true');
And the main.products_codes will references to this new field.
The main_system.products_codes have two colums:1-"field" as a field name2-"check" as a validate check before insert
intothe main table references.This check (2) will be executed before all "insert or update" (trigger) the
main table references (main.products_codes), for example if I want to UNIQUE
internal fields (inserted to me) I need to type "check" colums like: "NOT
EXISTS(select * from main.products_codes where field_name='internal' and
value='$value')";The $value variable will be used into another function (pltcl) called by
Trigger.The function:
--system_getcheckfieldvalue(schema name, table name, field name) will return
the CHECK necessary to Trigger.CREATE or REPLACE FUNCTION system_getcheckfieldvalue(text, text, text) returns
text as$$ set system_name "_system"; set schema_name $1; set table_name $2; set field_name $3; set select_query {select
"check"AS system_getcheckfieldvalue from }; set select_filter { where "field"='}; set close_select_filter "'"; set
point"."; spi_exec
$select_query$schema_name$system_name$point$table_name$select_filter$field_name$close_select_filter; return
$system_getcheckfieldvalue;$$language 'pltcl';SELECT system_getcheckfieldvalue('main','products_codes','internal');
--Will
return the query (not exists(select......SELECT system_getcheckfieldvalue('main','products_codes','barcode'); --Will
return "TRUE"
--system_checkfieldvalue (schema name, table name, field name, value
simulation) will return if the "value simulation" is Correct - True/FalseCREATE or REPLACE FUNCTION
system_checkfieldvalue(text, text, text, text)
returns bool as$$ set schema_name $1; set table_name $2; set field_name $3; set value $4; set result true; spi_exec
"select
system_getcheckfieldvalue('$schema_name','$table_name','$field_name')"; set sql_query [subst -nocommands
$system_getcheckfieldvalue];spi_exec "select $sql_query as result"; return $result;$$ language 'pltcl';SELECT
system_checkfieldvalue('main','products_codes','internal','COMPUTER-1');
--Will return FALSE (becouse there is a product with internal code COMPUTER-1SELECT
system_checkfieldvalue('main','products_codes','internal','COMPUTER-2');
--Will return TRUE (becouse there is NOT a product with internal code COMPUTER-2
Okay, but look the problem:
When I try to create a Trigger procedure to check (with the
system_checkfieldvalue() function) the Postgresql doesn't support the function
with parameters!!!
CREATE FUNCTION trigger_system_checkfieldvalue(schema_name text, table_name
text) RETURNS trigger AS$$ BEGIN select
system_checkfieldvalue(schema_name,table_name,NEW.field_name,NEW.value) as
result; IF NOT result THEN RAISE EXCEPTION 'The validate of the system field name is False'; END IF; RETURN NEW;
END;$$LANGUAGE plpgsql;
CREATE TRIGGER products_codes_checkfieldvalue BEFORE INSERT OR UPDATE ON
main.products_codes FOR EACH ROW EXECUTE PROCEDURE
trigger_system_checkfieldvalue('main','products_codes'); ------- ERROR: function trigger_system_checkfieldvalue()
doesnot exist
But the function trigger_system_checkfieldvalue() EXIST! With (text,text)
parameters.
I can't built the trigger for this table (main.products_codes) using the check
field in main_system.products_codes.What is wrong???
Sorry for the big text mail message, but I think if I did not put the database
definitions, it will be very difficult to understand.
Thanks (and sorry again)...
Lucas Vendramin,
Brazil
