Re: [SQL] calling a shell script from pl/pgsql
Jeff Barrett wrote: > Thanks for the suggestions. > > I am running 7.1.2 and going to 7.1.3 soon. > > If I use pl/tclu or pl/perlu I can call this executable from within the > code? > > I have a signifigant limitation, I cannot duplicate the action of the > programs I want to call in a program I write within postgres, I need to call > the executable (In this one case it is a shell script but I have others > where it is a binary). That's kind of a fuzzy explanation. If this "action" of your programs in any way affects data, that should be part of the triggers transaction, then what you want to do is broken by design. In detail, if the external program you're calling from the trigger connects to the database, updates it and terminates, the transaction that fired the trigger can still rollback, discarding all changes that caused the trigger to get fired. Thus, the database should look like the trigger never got fired - but how do you undo the changes of your external program? It connected to the database, did it's updates, committed and terminated. You'll have no chance to undo that. So if you really intend to do what it smells like, you're better off moving all the "external program"s logic into some function inside of the database, and call it from the external program as well as the trigger. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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
[SQL] table inheritance and foreign key troubles
I'm having a little trouble with some inherited tables and a foreign key. Here's a simplified case, to show the trouble. CREATE TABLE node ( node_id SERIAL NOT NULL, nameTEXT NOT NULL, PRIMARY KEY (node_id) ); -- works just fine CREATE TABLE users ( email TEXT NOT NULL ) INHERITS (node); -- so far so good CREATE TABLE item ( reason TEXT NOT NULL, author_id INT NOT NULL REFERENCES users (node_id) ) INHERITS (node); ERROR: UNIQUE constraint matching given keys for referenced table "users" not found Does this operation just require differing syntax, because the referenced field is inherited from another table, or is this not possible? Kevin Way ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SQL Query
Dear all, I want to do a query to my PostgreSQL database. I want to use it in a PHP web client that I have got, but also I have tried in the back-end. I have the following tables on my database ( I translate the field names from Spanish into English) cdroms --- code_cdroms items - code_items description cdroms_items - code_cdroms code_items loans -- code_loan reservations code_reservation The Query I want to execute is to search the cdroms that contains an item with the description provided by a form by the user. That query works fine, it is just a simple join among cdroms, items and cdroms_items. My problem shows up when I want to create two new fields to show whether the cdrom is on loan or is booked (someone made a reservation). After reading some books and web sites, I concluded to use the CASE statement ( I do not know if there is any other alternative). I tried it, and it worked, but only in the case there is any register in the loans or reservations tables, if there is not, the back-end says that you are trying to get more than 200 registers. What am I doing wrong? Here you have the SQL query. I translated the name of the fields, hope there is no mistake. I search for a string 'net' in the description field of the items table: SELECT cdroms.code_cdroms,cdroms_etiqueta,items.description,loans.code_loans, loans.tabletype,reservations.code_reservations,reservations.tabletype, (case when cdroms.code_cdroms=cdroms_items.code_cdroms and cdroms.code_cdroms=code_prestamo and cdroms_items.code_items=items.code_items and items.description like %net% then 'Yes' else 'No' end) as onloan, (case when cdroms.code_cdroms=cdroms_items.code_cdroms and cdroms.code_cdroms=code_loans and cdroms.code_cdroms= ANY (select code_reservations from reservations) and cdroms_items.code_items=items.code_items and items.description like %net% then 'Yes' else 'No' end) as booked from cdroms,items,cdroms_items,loans,reservations where cdroms.code_cdroms=cdroms_items.code_cdroms and loans.tabletype='cdroms' and cdroms_items.code_items=items.code_items and items.description like '%net%'; Sorry for my English. Hope you can understand. Is is a problem of concept or a sintax problem? Many thanks in advance Regards Miguel
Re: [SQL] table inheritance and foreign key troubles
On Tuesday, 11. September 2001 10:04, Kevin Way wrote: > I'm having a little trouble with some inherited tables and a foreign > key. Here's a simplified case, to show the trouble. > > CREATE TABLE node ( > node_id SERIAL NOT NULL, > nameTEXT NOT NULL, > PRIMARY KEY (node_id) > ); > -- works just fine > > CREATE TABLE users ( > email TEXT NOT NULL > ) INHERITS (node); > -- so far so good > > CREATE TABLE item ( > reason TEXT NOT NULL, > author_id INT NOT NULL REFERENCES users (node_id) > ) INHERITS (node); > ERROR: UNIQUE constraint matching given keys for referenced table > "users" not found That means, there is no UNIQUE constraing on users.node_id ;-) Since users inherits that field from node, just make node.node_id unique, or even a primary key. > Does this operation just require differing syntax, because the > referenced field is inherited from another table, or is this not > possible? In previous versions of Postgres it was possible to reference non-unique fields. With 7.1.x this is, correctly, no longer possible. Inheritance doesn't have anything do to with it in your case, though. Hope that helps, Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg ---(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] table inheritance and foreign key troubles
On Tuesday, 11. September 2001 11:12, I wrote before I thought: > On Tuesday, 11. September 2001 10:04, Kevin Way wrote: > > I'm having a little trouble with some inherited tables and a foreign > > key. Here's a simplified case, to show the trouble. > > > > CREATE TABLE node ( > > node_id SERIAL NOT NULL, > > nameTEXT NOT NULL, Sorry, I missed that line: > > PRIMARY KEY (node_id) > > ); > > -- works just fine > > > > CREATE TABLE users ( > > email TEXT NOT NULL > > ) INHERITS (node); > > -- so far so good > > > > CREATE TABLE item ( > > reason TEXT NOT NULL, > > author_id INT NOT NULL REFERENCES users (node_id) > > ) INHERITS (node); > > ERROR: UNIQUE constraint matching given keys for referenced table > > "users" not found > > That means, there is no UNIQUE constraing on users.node_id ;-) > Since users inherits that field from node, just make node.node_id > unique, or even a primary key. PG cannot inherit primary keys or unique constraints, I recall now. So you need to ensure uniqueness for users.node_id: CREATE TABLE users ( node_id INT4 UNIQUE, ) INHERITS (node ); The constraints of users.node_id and node.node_id get merged magically. This should work now on 7.1.3. I did test it this time. Best regards, Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] table inheritance and foreign key troubles
(hopefully this doesn't double post... stupid mail system) On Tue, 11 Sep 2001, Christof Glaser wrote: > On Tuesday, 11. September 2001 10:04, Kevin Way wrote: > > I'm having a little trouble with some inherited tables and a foreign > > key. Here's a simplified case, to show the trouble. > > > > CREATE TABLE node ( > > node_id SERIAL NOT NULL, > > nameTEXT NOT NULL, > > PRIMARY KEY (node_id) > > ); > > -- works just fine > > > > CREATE TABLE users ( > > email TEXT NOT NULL > > ) INHERITS (node); > > -- so far so good > > > > CREATE TABLE item ( > > reason TEXT NOT NULL, > > author_id INT NOT NULL REFERENCES users (node_id) > > ) INHERITS (node); > > ERROR: UNIQUE constraint matching given keys for referenced table > > "users" not found > > That means, there is no UNIQUE constraing on users.node_id ;-) > Since users inherits that field from node, just make node.node_id > unique, or even a primary key. Actually node.node_id looks to be a pkey, but primary keys/unique don't inherit, so users.node_id doesn't have the constraint. You'll need a primary key(node_id) on users as well (note that this won't actually enforce that values are unique across both node and users just within each table. See past discussions about inheritance and foreign keys... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] referencing oid impozsible ?
hello all I tried recently (pgsql 7.1.2) to establish the oid of one table as foreign key in another. To no avail : there was no uniqueness constraint on that column Naturally, it seems impossible to add a uniqueness constraint to such a system column. As far as i know, the oid is by nature unique, excepting for the case of wrapping of their sequence. Is there any workaround ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] referencing oid impozsible ?
On Tue, 11 Sep 2001 [EMAIL PROTECTED] wrote: > hello all > I tried recently (pgsql 7.1.2) to establish the oid of one table as foreign key > in another. > To no avail : there was no uniqueness constraint on that column > Naturally, it seems impossible to add a uniqueness constraint to such a system > column. > As far as i know, the oid is by nature unique, excepting for the case of > wrapping of their sequence. > Is there any workaround ? You could add a unique index on oid (which would basically be the unique constraint), but IIRC it still won't work on the system attribute. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] referencing oid impozsible ?
Patrick, > I tried recently (pgsql 7.1.2) to establish the oid of one table as > foreign key > in another. > To no avail : there was no uniqueness constraint on that column > Naturally, it seems impossible to add a uniqueness constraint to such > a system > column. > As far as i know, the oid is by nature unique, excepting for the case > of > wrapping of their sequence. > Is there any workaround ? Yes. Don't use the OID as your key. While it would be entirely possible for you add a unique constraint to the OID column, you do not want to use it as your key for a number of reasons previously discussed on this list (OID wraparound, problems with backup and restore, etc.). Instead, re-create the table with a new ID column of type SERIAL with a PRIMARY KEY constraint. This will automatically create an auto-incrementing sequence of unique values perfect for attaching foriegn keys. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
