Trigger with parameters - Mailing list pgsql-sql

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