Re: help with TCL function

From: darren(at)crystalballinc(dot)com
To: Jules Alberts <jules(dot)alberts(at)arbodienst-limburg(dot)nl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: help with TCL function
Date: 2003-09-11 16:34:25
Message-ID: Pine.LNX.4.44.0309111229460.10514-100000@thread.crystalballinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Ok you can user the info exists function that is part of TCL

i.e. if { [info exists NEW($1)] } {

or if 1 is the problem do
if { [info exists 1] } {

This will allow you to determine if it exists.

If you want to see what variables exist at this level elog [info vars] and
it will show you all variables currently accessable

Also you can array names or array get the contents or the NEW array then
foreach id value (if using array get) you can put them lower.

HTH
Darren

On Thu, 11 Sep 2003, Jules Alberts wrote:

> Op 11 Sep 2003 (0:45), schreef ljb <lbayuk(at)mindspring(dot)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!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
Darren Ferguson

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Vivek Khera 2003-09-11 16:42:40 Re: A Question About Insertions -- Performance
Previous Message Greg Stark 2003-09-11 16:24:20 How to avoid inlining subquery result columns