Re: help with TCL function

Поиск
Список
Период
Сортировка
От Jules Alberts
Тема Re: help with TCL function
Дата
Msg-id 20030911070051.C73B91CB1DC@koshin.dsl.systemec.nl
обсуждение исходный текст
Ответ на Re: help with TCL function  (ljb <lbayuk@mindspring.com>)
Ответы Re: help with TCL function  (Rolf Jentsch <RJentsch@electronicpartner.de>)
Re: help with TCL function  (darren@crystalballinc.com)
Список pgsql-general
Op 11 Sep 2003 (0:45), schreef ljb <lbayuk@mindspring.com>:

> I think you're missing something. When you use a Tcl function as a
> trigger, any arguments (like $1) are explicitly supplied in the CREATE
> TRIGGER command. You either define the trigger to call the function with
> a constant argument or not; there is no issue of NULL here or an
> optional argument. Show us your CREATE TRIGGER command.

-- This is the function

create or replace function tlow() returns trigger as '
  set NEW($1) [string tolower $NEW($1)]
  return [array get NEW]'
language 'pltcl';

-- Now the table on which I use it

create table bedrijf (
  code varchar unique not null check (code <> ''),
  kvk_nummer varchar,
) with oids;

-- And two triggers

create trigger tlow
  before insert or update
  on bedrijf for each row
  execute procedure tlow('code');

create trigger tlowkvk_nummer
  before insert or update
  on bedrijf for each row
  execute procedure tlow('kvk_nummer');

--

As you can see, the column kvk_nummer is allowed to be null. But /if/
it gets a value, that value should be converted to lowercase. So a sql
statement like this should be allowed:

insert into bedrijf (code) values ('FUBAR');

However this will fail because the trigger tlowkvk_nummer will call
tlow() without an argument. So I want to program tlow() defensively,
after all my DB logic should work, independenlty of anything that goes
on in the frontend.

I think it's a valid thing to expect from a DB or programming language.
Workarounds could be traversing all the frontend code to make sure no
NULL values are used or not using this kind of constraint at all. Not
really options IMO.

Thanks for any help!

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: 50K record DELETE Begins, 100% CPU, Never Completes 1 hour later
Следующее
От: Darko Prenosil
Дата:
Сообщение: Re: Picture with Postgres and Delphi