Re: [SQL] Trigger function to know which fields are being updated
Thank you, but my intension is to check whether the user supplies NAME when updating the record. For example the trigger shall allow statement 1 and block statement 2: 1. UPDATE COMPANY SET NAME = 'ABC', ADDRESS = '123 Drive' WHERE COMPANY_ID = 1; 2. UPDATE COMPANY SET ADDRESS = '123 Drive' WHERE COMPANY_ID = 1; I want the trigger to ensure that the user must provide value for NAME when updating this record. From: Jeff Eckermann <[EMAIL PROTECTED]> To: Bernard Cheung <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [SQL] Trigger function to know which fields are being updated Date: Mon, 10 May 2004 14:38:56 -0700 (PDT) --- Bernard Cheung <[EMAIL PROTECTED]> wrote: > I am writing a trigger function. How can I know > which fields are being > updated in the PL/SQL function? > > For example I have a table here: > > > CREATE TABLE COMPANY ( > COMPANY_ID VARCHAR(10) NOT NULL, > NAME VARCHAR(30), > ADDRESS VARCHAR(30)); > > I want to write a trigger to block all update > statements without updating > NAME. I tried the following code block and it > doesn't work: > > > IF TG_OP = ''UPDATE'' THEN > IF NEW.NAME IS NULL THEN > RAISE NOTICE ''Field NAME must be > provided!''; > END IF; > END IF; That should work. Perhaps "name" is not actually null, but rather an empty string? In that case, your test needs to be: "IF NEW.NAME IS NULL OR NEW.NAME = THEN..." > > Are there any functions like the Oracle's UPDATING() > predicate? > > Bernard Cheung > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ Linguaphone : Learning English? Get Japanese lessons for FREE http://go.msnserver.com/HK/46165.asp ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Trigger function to know which fields are being updated
--- Bernard Cheung <[EMAIL PROTECTED]> wrote: > Thank you, but my intension is to check whether the > user supplies NAME when > updating the record. > > For example the trigger shall allow statement 1 and > block statement 2: > > 1. UPDATE COMPANY SET NAME = 'ABC', ADDRESS = '123 > Drive' WHERE COMPANY_ID = > 1; > > 2. UPDATE COMPANY SET ADDRESS = '123 Drive' WHERE > COMPANY_ID = 1; > > I want the trigger to ensure that the user must > provide value for NAME when > updating this record. In case 2 above, the updated record will keep the same "name" value that it had previously. The logic that you need to use will depend on precisely what you want to happen. For example, if you expect that the name must change, then you can test "NEW.NAME = OLD.NAME". But if the name is not changing, this will not tell you anything, even if the user is in fact supplying the name. Depending on what rules you want to enforce, you may be better off doing the checking in your application. > > >From: Jeff Eckermann <[EMAIL PROTECTED]> > >To: Bernard Cheung <[EMAIL PROTECTED]>, > [EMAIL PROTECTED] > >Subject: Re: [SQL] Trigger function to know which > fields are being updated > >Date: Mon, 10 May 2004 14:38:56 -0700 (PDT) > > > > > >--- Bernard Cheung <[EMAIL PROTECTED]> wrote: > > > I am writing a trigger function. How can I know > > > which fields are being > > > updated in the PL/SQL function? > > > > > > For example I have a table here: > > > > > > > > > CREATE TABLE COMPANY ( > > > COMPANY_ID VARCHAR(10) NOT NULL, > > > NAME VARCHAR(30), > > > ADDRESS VARCHAR(30)); > > > > > > I want to write a trigger to block all update > > > statements without updating > > > NAME. I tried the following code block and it > > > doesn't work: > > > > > > > > > IF TG_OP = ''UPDATE'' THEN > > > IF NEW.NAME IS NULL THEN > > > RAISE NOTICE ''Field NAME must be > > > provided!''; > > > END IF; > > > END IF; > > > >That should work. Perhaps "name" is not actually > >null, but rather an empty string? In that case, > your > >test needs to be: "IF NEW.NAME IS NULL OR NEW.NAME > = > > THEN..." > > > > > > > > Are there any functions like the Oracle's > UPDATING() > > > predicate? > > > > > > Bernard Cheung > > > > > > ---(end of > > > broadcast)--- > > > TIP 1: subscribe and unsubscribe commands go to > >[EMAIL PROTECTED] > > _ > Linguaphone : Learning English? Get Japanese lessons > for FREE > http://go.msnserver.com/HK/46165.asp > __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Info
Hello, I have inserted a data bank in SQL server 2000 and from it have generated a Script. I would like to convert to this Script in postgresql, but I don't know like this does. I become to you very thankfully for every kind of information. Yours sincerely Sven ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Trigger function to know which fields are being updated
> --- Bernard Cheung <[EMAIL PROTECTED]> wrote: > > Thank you, but my intension is to check whether the user supplies > > NAME when > > updating the record. > > > > For example the trigger shall allow statement 1 and block statement > > 2: > > > > 1. UPDATE COMPANY SET NAME = 'ABC', ADDRESS = '123 Drive' WHERE > > COMPANY_ID = > > 1; > > > > 2. UPDATE COMPANY SET ADDRESS = '123 Drive' WHERE COMPANY_ID = 1; > > > > I want the trigger to ensure that the user must provide value for > > NAME when > > updating this record. > > > > >From: Jeff Eckermann <[EMAIL PROTECTED]> > > >To: Bernard Cheung <[EMAIL PROTECTED]>, > [EMAIL PROTECTED] > > >Subject: Re: [SQL] Trigger function to know which fields are being > > updated > > >Date: Mon, 10 May 2004 14:38:56 -0700 (PDT) > > > > > > > > >--- Bernard Cheung <[EMAIL PROTECTED]> wrote: > > > > I am writing a trigger function. How can I know > > > > which fields are being > > > > updated in the PL/SQL function? > > > > > > > > For example I have a table here: > > > > > > > > > > > > CREATE TABLE COMPANY ( > > > > COMPANY_ID VARCHAR(10) NOT NULL, > > > > NAME VARCHAR(30), > > > > ADDRESS VARCHAR(30)); > > > > > > > > I want to write a trigger to block all update > > > > statements without updating > > > > NAME. I tried the following code block and it > > > > doesn't work: > > > > > > > > > > > > IF TG_OP = ''UPDATE'' THEN > > > > IF NEW.NAME IS NULL THEN > > > > RAISE NOTICE ''Field NAME must be > > > > provided!''; > > > > END IF; > > > > END IF; > > > > > >That should work. Perhaps "name" is not actually > > >null, but rather an empty string? In that case, your > > >test needs to be: "IF NEW.NAME IS NULL OR NEW.NAME = > > > THEN..." > > > > > > > > > > > Are there any functions like the Oracle's UPDATING() > > > > predicate? > > > > > > > > Bernard Cheung > > > > > > > > ---(end of > > > > broadcast)--- > > > > TIP 1: subscribe and unsubscribe commands go to > > >[EMAIL PROTECTED] > > > > _ > > Linguaphone : Learning English? Get Japanese lessons for FREE > > http://go.msnserver.com/HK/46165.asp > > > > > > ---(end of > > broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > One way that comes to my mind is to check if is there any difference between the new.name and old.name... something like: if (old.name <> new.name) then -- something happening here... else -- nothing happened, or the name has not been changed. end if; regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Subqueries returning more than one value?
hi, Adam Witney wrote, On 5/11/2004 02:09: Hi, I am using a function in a subquery, this works ok: SELECT name, (SELECT p_my_func(1)) AS id FROM test; However I would like to have the function return 2 values into the main query... Something like this: SELECT name, (SELECT p_my_func(1)) AS (id, type) FROM test; Of course this gives the error: ERROR: subquery must return only one column Is there any way around this? SELECT name,t.* FROM (SELECT p_my_func(1)) AS t(id, type), test C. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Seconds To Time
Hi all, I'm a newby to PGSQL, I allways use MySQL, but now we use PGSQL, so i had to convert all the MySQL-Query's to PGSQL. In MySQL we have the function Sec_to_time(seconds). This will result a time-value like '40:30:00'. With this Query SELECT TIMESTAMP WITH TIME ZONE 'epoch' + sum(elapsed)/1000 * interval '1 second' from Table1 GROUP BY ... The result is a timestamp from the unixtime + the seconds elapsed. How can I convert this to a time-value? Thanks in advance ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Info
On Tue, 11 May 2004, [koi8-r] "Slava Ilijin[koi8-r] " wrote: > > > > Hello, I have inserted a data bank in SQL server 2000 and from it have > generated a Script. I would like to convert to this Script in > postgresql, but I don't know like this does. I become to you very > thankfully for every kind of information. Yours sincerely Sven Is this just data, or are you using TSQL in Microsoft, or some other language like php or vb to access the data? If it's in TransactSQL you'll have to learn a new language if you don't know on of the dozen or so supported by postgresql to write your own user defined function. Some of the languages available are plruby, plperl, plphp, plpgsql, plsql, plpython, pltcl, and of course, plC. plpgsql, pltcl, and plsql are the most "shaken out" with lots of development having been done in them. C requires a fair investment in your time and effort to begin development in, and generally isn't recommend for beginners. plpgsql is pretty much the default language, and a lot has been done in it. For some examples, see: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html IF you have written an app in vb/c# etc... you can probably just get the oledb connector and start hacking your code around that. http://gborg.postgresql.org/project/oledb/projdisplay.php Also, the mono project appears to be working on an OLEDB connector too that might work. Or, you could abandon whatever you've used and start it over in php, perl, C, or whatever else you want, using the native postgresql connection libs for those languages... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] \set
Hi, How to use an internal variable? Original question was how to set a variable in postgresql? If I want to set a variable like start_date='2004-05-10'; How could I use it in my SQL statement? E.g. Db> set start_date '2004-05-10' Db> select start_date as 'start date'; It's not executable! Thanks. Jie Liang ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
