Re: [SQL] Create Public Schema
[EMAIL PROTECTED] <[EMAIL PROTECTED]> schrieb: > Hello all, > > I'm trying to create a schema that contains the default tables, functions, > etc. of the public schema. Using pgAdmin, when I create a schema, it is blank 1. make a dump from the public-schema 2. edit the dump and change 'public' to 'foobar' (the new schema-name) 3. restore the schema from dump Now you have a exactly copy from 'public' with name 'foobar'. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Trigger / rule question
Hi, Are a trigger or rule part of a query, that is: 1. Does an insert, update or delete statement return before or after an "After" trigger (is such a trigger or rule synchronous or a-synchronous? 2. Is there a concept of a rule or trigger that fails? If so, if a trigger or rule fails, does the insert, update or delete statement (transaction) fail? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Create Public Schema
On Sat, 2005-11-19 at 20:03 -0700, [EMAIL PROTECTED] wrote: > Hello all, > > I'm trying to create a schema that contains the default tables, > functions, etc. of the public schema. Using pgAdmin, when I create a > schema, it is blank - has no associated aggregates, tables, etc. How > would I create additional schems within a database that mirror the > public schema. > I guess your best bet would be to manually create all the scripts for your database/schema objects. Use pg_dump to create a 'database schema snapshot', and then use pg_restore -L to create scripts for objects you desire. Mike ---(end of broadcast)--- TIP 1: 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] Is it possible to redirect an update/insert/delete to a different table?
Andy Ballingall wrote: > I've looked through rules, and as far as I can make out, they are > only useful for explicit actions. I'm looking for something that > behaves as though it simply substitutes the table name for a > different table name before executing the command, no matter what the > command looks like. You can write a couple of rules for that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Trigger / rule question
Joost Kraaijeveld wrote: > 1. Does an insert, update or delete statement return before or after > an "After" trigger (is such a trigger or rule synchronous or > a-synchronous? Synchronous > 2. Is there a concept of a rule or trigger that fails? > If so, if a trigger or rule fails, does the insert, update or delete > statement (transaction) fail? Yes -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: 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] Is it possible to redirect an update/insert/delete to a different table?
> I've looked through rules, and as far as I can make out, they are only > useful for explicit actions. I'm looking for something that behaves as > though it simply substitutes the table name for a different table name > before executing the command, no matter what the command looks like. > Make 3 rules (INSERT/UPDATE/DELETE), 4 if you want SELECT as well... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
Hello Peter, I'm glad it's possible, but I can't see how from the documentation. Say if I have a table called 'apples' and a table called 'pears'. What would the rule look like that would remap all updates on apples so that they were applied to pears instead? Thanks, Andy - Andy Ballingall wrote: > I've looked through rules, and as far as I can make out, they are > only useful for explicit actions. I'm looking for something that > behaves as though it simply substitutes the table name for a > different table name before executing the command, no matter what the > command looks like. You can write a couple of rules for that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Trigger / rule question
Hi Peter, On Sun, 2005-11-20 at 12:08 +0100, Peter Eisentraut wrote: > Joost Kraaijeveld wrote: > > 1. Does an insert, update or delete statement return before or after > > an "After" trigger (is such a trigger or rule synchronous or > > a-synchronous? > > Synchronous > > > 2. Is there a concept of a rule or trigger that fails? > > If so, if a trigger or rule fails, does the insert, update or delete > > statement (transaction) fail? > > Yes OK, thanks for the answer. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
Hello Jaime, I'm still not quite clear. Say I have a number of different updates on a table 'apples' in my code, including: UPDATE apples set pips=6 and color='yellow' where id=3; UPDATE apples set size=10 where id=6; What would a rule look like which, when *any* update is attempted on the apples table, will instead apply the update to a different table - 'pears'. I get this far: CREATE rule pears_instead_of_apples AS ON UPDATE TO apples DO INSTEAD UPDATE INTO pears .; What do I put where the . is, so that the rule will transparently update the pears table with whatever values happened to be defined by the original update command? Is there a special keyword that I've missed? Regards, Andy Ballingall -Original Message- From: Jaime Casanova [mailto:[EMAIL PROTECTED] Sent: 20 November 2005 14:23 To: [EMAIL PROTECTED] Cc: pgsql-sql@postgresql.org Subject: Re: Is it possible to redirect an update/insert/delete to a different table? > I've looked through rules, and as far as I can make out, they are only > useful for explicit actions. I'm looking for something that behaves as > though it simply substitutes the table name for a different table name > before executing the command, no matter what the command looks like. > Make 3 rules (INSERT/UPDATE/DELETE), 4 if you want SELECT as well... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
Andy Ballingall <[EMAIL PROTECTED]> schrieb: > Hello Peter, > > I'm glad it's possible, but I can't see how from the documentation. > > Say if I have a table called 'apples' and a table called 'pears'. > > What would the rule look like that would remap all updates on apples so that > they were applied to pears instead? create rule apples_pears_update as on update to apples do instead update pears set name= NEW.name where id=NEW.id ; test=# select * from apples ; id | name +-- 1 | a (1 row) test=# select * from pears ; id | name +-- 1 | b (1 row) test=# update apples set name = 'c' where id = 1; UPDATE 1 test=# select * from pears ; id | name +-- 1 | c (1 row) http://www.postgresql.org/docs/8.1/interactive/rules-update.html HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
Andy Ballingall <[EMAIL PROTECTED]> schrieb: > Hello Jaime, > > I'm still not quite clear. > > Say I have a number of different updates on a table 'apples' in my code, > including: > > UPDATE apples set pips=6 and color='yellow' where id=3; > UPDATE apples set size=10 where id=6; > > What would a rule look like which, when *any* update is attempted on the > apples table, will instead apply the update to a different table - 'pears'. Try it. test=# create table apples (id int, name1 text, name2 text); CREATE TABLE test=# create table pears (id int, name1 text, name2 text); CREATE TABLE test=# create rule apples_pears_update as on update to apples do instead update pears set name1= NEW.name1, name2=NEW.name2 where id=NEW.id ; CREATE RULE test=# insert into apples values (1, 'a', 'a'); INSERT 0 1 test=# insert into pears values (1, 'a', 'a'); INSERT 0 1 test=# test=# update apples set name1='b' where id = 1; UPDATE 1 test=# select * from pears ; id | name1 | name2 +---+--- 1 | b | a (1 row) test=# update apples set name2='c' where id = 1; UPDATE 1 test=# select * from pears ; id | name1 | name2 +---+--- 1 | a | c (1 row) test=# update apples set name1='e', name2='e' where id = 1; UPDATE 1 test=# select * from pears ; id | name1 | name2 +---+--- 1 | e | e (1 row) > > -Original Message- Please, no top-posting. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
Hi Andreas, The rule you've given only works for an update which changes the name. If I do another update which changed the colour instead of the name, that rule wouldn't do the right thing. Instead, I'm looking for something which, with a single 'rule' (or whatever the mechanism ends up being), intercepts *any* update on apples, and applies the changes to the pears table instead, as if the only change that occurred was a change of table name. I can achieve this in the application which generates the sql commands, but someone else suggested it was possible with rules, but it may not be the case. Thanks! Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Kretschmer Sent: 20 November 2005 16:17 To: pgsql-sql@postgresql.org Subject: Re: [SQL] Is it possible to redirect an update/insert/delete to a different table? Andy Ballingall <[EMAIL PROTECTED]> schrieb: > Hello Peter, > > I'm glad it's possible, but I can't see how from the documentation. > > Say if I have a table called 'apples' and a table called 'pears'. > > What would the rule look like that would remap all updates on apples so that > they were applied to pears instead? create rule apples_pears_update as on update to apples do instead update pears set name= NEW.name where id=NEW.id ; test=# select * from apples ; id | name +-- 1 | a (1 row) test=# select * from pears ; id | name +-- 1 | b (1 row) test=# update apples set name = 'c' where id = 1; UPDATE 1 test=# select * from pears ; id | name +-- 1 | c (1 row) http://www.postgresql.org/docs/8.1/interactive/rules-update.html HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
Andy Ballingall <[EMAIL PROTECTED]> schrieb: > Hi Andreas, > > The rule you've given only works for an update which changes the name. > > If I do another update which changed the colour instead of the name, that > rule wouldn't do the right thing. Right. > Instead, I'm looking for something which, with a single 'rule' (or whatever > the mechanism ends up being), intercepts *any* update on apples, and applies > the changes to the pears table instead, as if the only change that occurred > was a change of table name. My example was simple, right. > I can achieve this in the application which generates the sql commands, but > someone else suggested it was possible with rules, but it may not be the > case. Inside the rule you have the NEW-Variable with all values to update. Read my other answer. I'm not sure, perhaps it is possible to write a more generic rule. > > -Original Message- Please, no top-posting with silly fullquote below. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 1: 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] Is it possible to redirect an update/insert/delete to a different table?
>Try it. [snipped example] Ah. Basically, you set up the rule to assign every column, and if the update doesn't redefine some columns, then it still works. I didn't understand that you could get the rule to work generically like this. I'll presume that the rule will need amending if the table column definition later changes. (E.g. if I add 'stalk_length' to my apples and pears tables)... Thanks very much for your help. Andy ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Please help to wite the constraint.
Hello, everybody! I don't whether it is possible to do the following but anyway I can't. I need to write a constraint as described below. Here are four tables: CREATE TABLE countries (id SERIAL, name VARCHAR(255), PRIMARY KEY (id) ); CREATE TABLE countries_names (id INT NOT NULL, id_lang INT NOT NULL, name VARCHAR(255), PRIMARY KEY (id, id_lang), FOREIGN KEY (id) REFERENCES countries (id), FOREIGN KEY (id_lang) REFERENCES lang (id) ); CREATE TABLE contact_info_fields (id SERIAL, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE contact_info_records (id_user INT NOT NULL, id_ci_field INT NOT NULL, id_lang INT NOT NULL, value TEXT, PRIMARY KEY (id_user, id_ci_field, id_lang), FOREIGN KEY (id_user) REFERENCES users (id), FOREIGN KEY (id_ci_field) REFERENCES contact_info_fields (id), FOREIGN KEY (id_lang) REFERENCES lang (id) ); The last table contains contact information records of different types. These types are taken from the table contact_info_fields. In particular, there can be the type 'country' say with id=1. Then the contact_info_records table can contain the following info: id_ci_field=1 and the VALUE field must contain a country's name but ONLY if it exists in the countries table (column 'name'). So it turns out to be a wierd foreign key. Is it possible to write such a constraint? Thanks! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?
On Sunday 20 November 2005 09:15 am, Andy Ballingall wrote: It works because of the way updates are done. When you do an update two versions of the row exist. The OLD version is the row as it existed before you updated. The NEW version contains the entire version with the update changes. The key thing to remember is the the NEW version contains both those fields that have changed as well as those that have not. So the UPDATE rule just passes along all the fields named in it regardless of whether they changed or not. It would be a good idea to read the following section of the manual (http://www.postgresql.org/docs/8.0/interactive/rules.html) as it explains when the rule picks up the values in the fields. This differs according to the type of rule. > >Try it. [snipped example] > > Ah. Basically, you set up the rule to assign every column, and if the > update doesn't redefine some columns, then it still works. I didn't > understand that you could get the rule to work generically like this. > > I'll presume that the rule will need amending if the table column > definition later changes. (E.g. if I add 'stalk_length' to my apples and > pears tables)... > > Thanks very much for your help. > > Andy > > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
