[SQL] evaluating values of attributes from computed attribute names in trigger function
Hi, I need to find all changed fields in a trigger function. I found out how to get the names of all attributes within plpgsql, but I cannot get the value of NEW and OLD for the attribute with that name. That is: assume there is NEW.foo and OLD.foo let the name 'foo' be computed and stored in variable attrname: attrname='foo' Then, if I write NEW.attrname, it is confused with an attribute called 'attrname'. So how can I get the changed fields in my row? Please see my function below. Thank you very much, Markus CREATE FUNCTION trg_001() RETURNS OPAQUE AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT a.attname AS atr FROM pg_attribute a,pg_class c WHERE a.attrelid = c.oid AND a.attnum > 0 AND c.relname=TG_RELNAME LOOP IF NEW.atr <> OLD.atr THEN INSERT INTO test (txt) VALUES (rec.atr); END IF; END LOOP; RETURN NEW; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] possible to lock a single row in table?
Hello! i have a lots of threads that work on a table, making insertions, updates removes. now i certain cases its important to keep data integrity, so i looked into locks all i found was a lock function that locks the entire table now even in shared mode, if i understand it well this means that as long as the locking procedure isn't finished, no other thread can update or delete data, right? Thats really harsh, since there are really an awful lot of threads isn't it possible to lock only a specific row? and what happens to other insert queries whilst the lock is operational? Are they postponed and the valling thread waits, or does the call return with an error? -- ciao bboett == [EMAIL PROTECTED] http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett === the total amount of intelligence on earth is constant. human population is growing ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] set datestyle to European PROBLEM
I have the defaul installation of postgres 7.0.3 and on another machine 7.1.2 on redhat 7.1 I cannont get the date in correct form: dbme=# select data_ar from equipment limit 5; data_ar 2001-11-05 2001-05-17 2001-05-28 2001-05-28 2001-05-22 (5 rows) then: dbme=# set datestyle to European; SET VARIABLE dbme=# select data_ar from equipment limit 5; data_ar 2001-11-05 2001-05-17 2001-05-28 2001-05-28 2001-05-22 (5 rows) Is this a bug ? I think i should get dd-mm-yyy date format and not -mm-dd Is postgres using ISO date format as default ? Thanks Alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] possible to lock a single row in table?
On Wed, 20 Jun 2001, Bruno Boettcher wrote: > i have a lots of threads that work on a table, making insertions, > updates removes. > > now i certain cases its important to keep data integrity, so i looked > into locks What kind of data integrity are you trying to implement? > all i found was a lock function that locks the entire table > > now even in shared mode, if i understand it well this means that as long > as the locking procedure isn't finished, no other thread can update or > delete data, right? Thats really harsh, since there are really an awful > lot of threads > > isn't it possible to lock only a specific row? Yes. Look at SELECT ... FOR UPDATE. That should lock those rows matched such that another transaction that attempts to select for update, update or delete those rows waits until the locking transaction finishes. > and what happens to other insert queries whilst the lock is operational? > Are they postponed and the valling thread waits, or does the call return > with an error? IIRC, postponed until the transaction that made the lock commits or rolls back. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Extracting date from epoche
Hi I have some data that is supose to be a date but in ecpoche format. How can I reonvert it to data format. Thanks in advance for help. Regards, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com
[SQL] Functions and Triggers
Title: Functions and Triggers Is there anyone that can point me into the right direction for creating a function and trigger that will do something along these lines: I have a table that has router card types and a status on them: TEST_RECORD CARD STATUS 1. er16-04 0 2. er16-04 er16-08 1 3. ssr8000 ssr8600 0 4. er16-04 1 5. er16-04 ssr8600 0 6. er-16-04 1 I need a trigger with a function that will check the card type and status and from there determine the over all status of the test in the test_table. Each tuple represents one test on a specific card(s) and the status is pass or fail. Here the overall status that would be updated in the test_table status be fail, because of the ssr8600 on line 5. I appreciate any suggestions and/or comments. RAY HUNTER Automated Test Group Software Support Engineer ENTERASYS NETWORKS Internal: 53888 Phone: 801 887-9888 Fax: 801 972-5789 Cellular: 801 698-0622 E-mail: [EMAIL PROTECTED] www.enterasys.com
[SQL] Functions and Triggers
Title: Functions and Triggers Is there anyone that can point me into the right direction for creating a function and trigger that will do something along these lines: I have a table that has router card types and a status on them: TEST_RECORD CARD STATUS 1. er16-04 0 2. er16-04 er16-08 1 3. ssr8000 ssr8600 0 4. er16-04 1 5. er16-04 ssr8600 0 6. er-16-04 1 I need a trigger with a function that will check the card type and status and from there determine the over all status of the test in the test_table. Each tuple represents one test on a specific card(s) and the status is pass or fail. Here the overall status that would be updated in the test_table status be fail, because of the ssr8600 on line 5. I appreciate any suggestions and/or comments. RAY HUNTER Automated Test Group Software Support Engineer ENTERASYS NETWORKS Internal: 53888 Phone: 801 887-9888 Fax: 801 972-5789 Cellular: 801 698-0622 E-mail: [EMAIL PROTECTED] www.enterasys.com
[SQL] How to build a TRIGGER in POSTGERSQL
Title: How to build a TRIGGER in POSTGERSQL Hi , I need some help in building trigger and information about sysdate (System date), any help will be appreciate. 1) I need to build a trigger that every time I insert into the table one of the columns will get +1 number. I have it in ORACLE ( see below the create of the sequence and the trigger) but how you can do it in PostGer SQL) CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 99 CYCLE; CREATE TRIGGER AD_MNG_SYS_TRIG BEFORE INSERT ON AD_MNG_SYS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual; END; / 2) what is equal to 'sysdate' (to get/put the system date in a table) in PostGerSQL. Thanks, Ilan
[SQL] pl/pgsql question (functions)
Greetings,
I am new to pl/pgsqm (I did some SQL programming at school, now 8 years
ago so that part is rusty too ;-))
Anycase, I have a question concerning creating your own functions in
pl/pgsql.
This is my situation:
I have two tables:
- 'hosts': containing 'hostname' (primary key) and 'ip_address' (unique
and non null).
- 'aliases': containing 'aliasname' (primary key) and 'hostname'(unique
and non null).
- aliasname.hostname is a foreign reference to hosts.hostname
Further, I've set up a trigger function in both hosts and aliases; so that
a name cannot be in both tables.
So far, so good.
Now, I would like to create a function 'get_ip_address' that ... euh ...
retrieves an IP-address (one argument: hostname).
- If the hostname is in the table 'hosts', return the corresponding
IP-address.
- If the hostname is in the table 'aliases', return the IP-address of the
corresponding hostname in 'hosts'.
- If the hostname is in neither table, return nothing.
(See program below):
The 'problem' is in the case where the data in in neither field. How do I
program this?
- If there is no 'RETURN' statement for that case; I get an error
'function terminated without RETURN'.
- Just , or all produce an syntax-error.
- When I do '' (ret being the result of the last query, being
the query in the 'aliases' table; I do NOT get an error; but the function
does return something (an empty row).
This I don't like for two reasons:
1/ When you do 'select ... from ... where ...', and the query doesn't
'find' anything; you get NOTHING (no rows).
When I do get_ip_addr('something_that_does_not_exist'); I do get
SOMETHING: one row (containing an empty field).
2/ When another function uses the "get_ip_addr('some_host')" function; I
cannot use 'IF NOT FOUND ...'; as -even when 'some_host' does not exist,
the function returns something. (hence, the 'IF NOT FOUND' case is never
followed).
So, does anybody any idea how to 'fix' this?
For some reason, I get the feeling I have the wrong 'concept' of functions
in pl/pgsql.
I am using functions in the wrong 'way'?
Here's the program:
--- cut here --- begin --- cut here ---
DECLARE
ret inet;
BEGIN
select into ret
ipaddr from hosts
where hostname = $1;
IF FOUND
THEN
RETURN ret;
ELSE
select into ret
ipaddr from hosts,aliases
where
hosts.hostname = aliases.hostname
AND aliases.aliasname = $1;
IF FOUND
THEN
RETURN ret;
ELSE
-- Problem, what should I put here ???
RETURN;
END IF;
END IF;
END;
--- cut here --- end --- cut here ---
Cheerio! Kr. Bonne.
--
KB905-RIPE Belgacom IP networking
(c=be,a=rtt,p=belgacomgroup,s=Bonne,g=Kristoff) Internet, IP and IP/VPN
[EMAIL PROTECTED] Faxbox : +32 2 2435122
---(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] set datestyle to European PROBLEM
Alessandro Rossi wrote: > I have the defaul installation of postgres 7.0.3 and on another machine > 7.1.2 on redhat 7.1 > > I cannont get the date in correct form: . > Is this a bug ? > > I think i should get dd-mm-yyy date format and not -mm-dd > > Is postgres using ISO date format as default ? I had the same problem ... but I solved ... (I use PostgreSQL 7.1 on RH 7.1 installed from rpm): In "/etc/rc.d/init.d/postgresql" I modify in "start" arm ... from: su -l postgres -c "LC_ALL=C /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null to su -l postgres -c "LC_ALL=C /usr/bin/pg_ctl -D $PGDATA -o '-i -o -e' -p /usr/bin/postmaster start >/dev/null 2>&1" < /dev/null and, after I connect my application to the database, the first command is: SET DATESTYLE TO 'Postgres'; and work fine ... > Thanks > Alex George Moga, Data Systems Srl, Slobozia, ROMANIA ---(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] How to check if a table exists from functions.
On Tue, 19 Jun 2001, Mikael Kjellström wrote: > > Maybe I am doing this the wrong way, but how do I check if a table exist? > > This works from psql: > > select > relname > from > pg_class > where > relname = 'tablename' > > > But if I try to do the same from a PL/PGSQL stored procedure I get the > following error message: > > ERROR: unexpected SELECT query in exec_stmt_execsql() > > Isn't select's allowed from stored procedures? You'll want to use something like: declare foo name; begin ... select relname INTO foo from pg_class where relname=''tablename''; ... end; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] commentds on redhats new database
hello: i thought id find out what people think about this: http://www.zdnet.com/zdnn/stories/news/0,4586,2778706,00.html?chkpt=zdnn_rt_latest which basically points out that redhat will be making their own database based off of postgres7.1 my main concern is that this could cause some sort of splintering of the code base could this potentially happen? what other concerns could there be? ---(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] commentds on redhats new database
On Wed, Jun 20, 2001 at 03:28:34PM -0700, clayton cottingham wrote: > hello: > > i thought id find out what people think about this: > http://www.zdnet.com/zdnn/stories/news/0,4586,2778706,00.html?chkpt=zdnn_rt_latest > > which basically points out that redhat will be making their own database > based off of postgres7.1 Quoting: The Red Hat Database, as it is likely to be called, is based on PostgreSQL 7.1. Included in the package will be Red Hat Installer, which will aid users in quickly installing the database, robust transaction support and advanced locking capabilities. It will comply with SQL92, ODBC and JDBC APIs. > > my main concern is that this could cause some sort of splintering of the > code base I'll be interesting to see how their offering differs from GreatBridge. And if their going to claim SQL'92 compliance, have they done all the SCHEMA support work? ;-) > > could this potentially happen? Sure, we're BSD, so they're under no obligation to share any code changes with us. Haven't noticed many new @redhat.com email address over on HACKERS, though, so I wonder if they've done much work on the core. > > what other concerns could there be? > Getting bug reports from code we can't look at. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] commentds on redhats new database
"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: >> could [a fork] potentially happen? > Sure, we're BSD, so they're under no obligation to share any code changes > with us. I really doubt that RedHat is silly enough to want to fork the code. They haven't forked Linux, gcc, gdb, etc, but have been upstanding contributors to all those projects; why would they do it differently with Postgres? > Haven't noticed many new @redhat.com email address over on HACKERS, > though, so I wonder if they've done much work on the core. Some folk at RedHat's Toronto offices are gearing up to contribute work; I was up there to talk with them just a couple weeks ago. They've not been real visible yet though ... (hey Patrick et al: if you're reading this, it's past time to introduce yourselves). regards, tom lane ---(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] commentds on redhats new database
On Thu, Jun 21, 2001 at 01:32:10AM -0400, Tom Lane wrote: > "Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > >> could [a fork] potentially happen? > > > Sure, we're BSD, so they're under no obligation to share any code changes > > with us. > > I really doubt that RedHat is silly enough to want to fork the code. > They haven't forked Linux, gcc, gdb, etc, but have been upstanding > contributors to all those projects; why would they do it differently > with Postgres? Hey, he did say _potentially_. I agree that it's not at all _likely_. > > > Haven't noticed many new @redhat.com email address over on HACKERS, > > though, so I wonder if they've done much work on the core. > > Some folk at RedHat's Toronto offices are gearing up to contribute work; > I was up there to talk with them just a couple weeks ago. They've not > been real visible yet though ... (hey Patrick et al: if you're reading > this, it's past time to introduce yourselves). Sounds cool. The more the merrier, I say. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
