Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Hi Dmitriy Igrishin, Thanks. That's a good idea too. From: Dmitriy Igrishin [mailto:dmit...@gmail.com] Sent: Saturday, February 19, 2011 3:31 PM To: gna...@zoniac.com Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause? Hey Gnanakumar, You can wrap you UPDATE query into SQL function returning TABLE, e.g: CREATE OR REPLACE FUNCTION public.update_mytable() RETURNS TABLE(email text, column1 text, column2 text, column3 text) LANGUAGE sql AS $function$ UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; $function$; Next you can write, e.g: SELECT DISTINCT * FROM (SELECT update_mytable()) AS foo(email, column1, column2, column3); And so on. 2011/2/18 Gnanakumar gna...@zoniac.com Any ideas? -Original Message- From: Gnanakumar [mailto:gna...@zoniac.com] Sent: Thursday, February 17, 2011 12:36 PM To: pgsql-sql@postgresql.org Subject: Is it possible to get DISTINCT rows from RETURNING clause? Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? MYTABLE columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- // Dmitriy. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Hey Gnanakumar, You can wrap you UPDATE query into SQL function returning TABLE, e.g: CREATE OR REPLACE FUNCTION public.update_mytable() RETURNS TABLE(email text, column1 text, column2 text, column3 text) LANGUAGE sql AS $function$ UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; $function$; Next you can write, e.g: SELECT DISTINCT * FROM (SELECT update_mytable()) AS foo(email, column1, column2, column3); And so on. 2011/2/18 Gnanakumar gna...@zoniac.com Any ideas? -Original Message- From: Gnanakumar [mailto:gna...@zoniac.com] Sent: Thursday, February 17, 2011 12:36 PM To: pgsql-sql@postgresql.org Subject: Is it possible to get DISTINCT rows from RETURNING clause? Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? MYTABLE columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- // Dmitriy.
Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Any ideas? -Original Message- From: Gnanakumar [mailto:gna...@zoniac.com] Sent: Thursday, February 17, 2011 12:36 PM To: pgsql-sql@postgresql.org Subject: Is it possible to get DISTINCT rows from RETURNING clause? Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? MYTABLE columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? MYTABLE columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is this possible?
On Mon, Feb 16, 2009 at 7:36 PM, johnf jfabi...@yolo.com wrote: Hi, I'm not to sure this is possible. I need to replace a primary key (pkid) with the value of a different field. I have pkid = 200 attendid = 301 I need the pkid = 301 But there may or may not be a pkid that already exist that has the value of 301. The attendid is unique and the pkid data type is serial (has a sequence). If the FK is on update cascade just update it. and setval() the sequence to be max(pkid)+1. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is this possible?
On Monday 16 February 2009 10:32:26 pm A. Kretschmer wrote:
In response to johnf :
Hi,
I'm not to sure this is possible.
I need to replace a primary key (pkid) with the value of a different
field. I have
pkid = 200
attendid = 301
I need the pkid = 301
But there may or may not be a pkid that already exist that has the value
of 301. The attendid is unique and the pkid data type is serial (has a
sequence).
Thanks for the help.
You can do that within a transaction and dropping the pk-constraint:
test=*# \d foo
Table public.foo
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('foo_id_seq'::regclass)
i | integer |
Indexes:
foo_pkey PRIMARY KEY, btree (id)
foo_i_key UNIQUE, btree (i)
test=*# select * from foo;
id | i
+---
1 | 2
2 | 1
3 | 3
(3 rows)
test=*# alter table foo drop constraint foo_pkey;
ALTER TABLE
test=*# update foo set id=i;
UPDATE 3
test=*# alter table foo add primary key(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
foo_pkey for table foo ALTER TABLE
test=*# \d foo
Table public.foo
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('foo_id_seq'::regclass)
i | integer |
Indexes:
foo_pkey PRIMARY KEY, btree (id)
foo_i_key UNIQUE, btree (i)
test=*# select * from foo;
id | i
+---
2 | 2
1 | 1
3 | 3
(3 rows)
HTH, Andreas
Wow that looks like it will work - thanks.
When you say 'within a transaction' do you mean starting with
Begin and using commit?
--
John Fabiani
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is this possible?
In response to johnf : Wow that looks like it will work - thanks. When you say 'within a transaction' do you mean starting with Begin and using commit? Exactly. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Is this possible?
Hi, I'm not to sure this is possible. I need to replace a primary key (pkid) with the value of a different field. I have pkid = 200 attendid = 301 I need the pkid = 301 But there may or may not be a pkid that already exist that has the value of 301. The attendid is unique and the pkid data type is serial (has a sequence). Thanks for the help. -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is this possible?
In response to johnf :
Hi,
I'm not to sure this is possible.
I need to replace a primary key (pkid) with the value of a different field.
I have
pkid = 200
attendid = 301
I need the pkid = 301
But there may or may not be a pkid that already exist that has the value of
301. The attendid is unique and the pkid data type is serial (has a
sequence).
Thanks for the help.
You can do that within a transaction and dropping the pk-constraint:
test=*# \d foo
Table public.foo
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('foo_id_seq'::regclass)
i | integer |
Indexes:
foo_pkey PRIMARY KEY, btree (id)
foo_i_key UNIQUE, btree (i)
test=*# select * from foo;
id | i
+---
1 | 2
2 | 1
3 | 3
(3 rows)
test=*# alter table foo drop constraint foo_pkey;
ALTER TABLE
test=*# update foo set id=i;
UPDATE 3
test=*# alter table foo add primary key(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index foo_pkey
for table foo
ALTER TABLE
test=*# \d foo
Table public.foo
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('foo_id_seq'::regclass)
i | integer |
Indexes:
foo_pkey PRIMARY KEY, btree (id)
foo_i_key UNIQUE, btree (i)
test=*# select * from foo;
id | i
+---
2 | 2
1 | 1
3 | 3
(3 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Is it possible to only allow deletes from a table via referential integrity cascades?
I have two tables joined by a foreign key constraint: CREATE TABLE test_master( id SERIAL PRIMARY KEY, foo TEXT ); CREATE TABLE test_detail( id SERIAL PRIMARY KEY, master BIGINT NOT NULL REFERENCES test_master(id) ON DELETE CASCADE ON UPDATE CASCADE, bar TEXT ); Is there a way to block deletes on the test_detail table that will only allow rows to be deleted if it is the result of deleting the corresponding test_master record? In other words, I'd like to disallow direct DELETE commands like this: DELETE FROM test_detail WHERE id = 1; while allowing a command like DELETE FROM test_master WHERE id = 1; to subsequently delete via CASCADE all test_detail rows that reference test_master # 1. I've tried using rules and revoking privileges, but both of these approaches fail when trying to delete from test_master. Thanks in advance, Chris -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Dynamic prepare possible in plpgsql?
On May 1, 2007, at 12:16 PM, Jonah H. Harris wrote: On 5/1/07, Collin Peters [EMAIL PROTECTED] wrote: Is it faster to use PREPARE for the various INSERT statements inside a plpgsql function? Perhaps I am wrong and it does its PREPARE work when the function is parsed. IIRC, PLpgSQL automagically prepares each statement behind the scenes on the first use. BTW, a good use for a version of EXECUTE that accepted parameters is the trigger on a partitioned table to direct inserts to the appropriate partition. Currently, you have to quote_literal(coalesce (NEW.field, 'NULL')) in the dynamic statement. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Dynamic prepare possible in plpgsql?
So if I have an INSERT inside a LOOP in a plpgsql function, it is only prepared once? Regards, Collin On 5/1/07, Jonah H. Harris [EMAIL PROTECTED] wrote: On 5/1/07, Collin Peters [EMAIL PROTECTED] wrote: Is it faster to use PREPARE for the various INSERT statements inside a plpgsql function? Perhaps I am wrong and it does its PREPARE work when the function is parsed. IIRC, PLpgSQL automagically prepares each statement behind the scenes on the first use. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Dynamic prepare possible in plpgsql?
Collin Peters [EMAIL PROTECTED] writes: So if I have an INSERT inside a LOOP in a plpgsql function, it is only prepared once? Once per session, yes (barring some special cases like polymorphic functions and trigger functions, which would get prepared once per session per calling situation) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Dynamic prepare possible in plpgsql?
I have a plpgsql function which is doing a loop over one table of user data and then inserting that data in various tables. Example: loop over user table (temp data) insert into users1 table insert into users2 table etc end loop Is it faster to use PREPARE for the various INSERT statements inside a plpgsql function? Perhaps I am wrong and it does its PREPARE work when the function is parsed. Collin On 4/30/07, Tom Lane [EMAIL PROTECTED] wrote: Collin Peters [EMAIL PROTECTED] writes: Is it possible to have a dynamic PREPARE statement in plpgsql? Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE commands, but it seems awfully brute-force. What do you really need to accomplish here? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Dynamic prepare possible in plpgsql?
On 5/1/07, Collin Peters [EMAIL PROTECTED] wrote: Is it faster to use PREPARE for the various INSERT statements inside a plpgsql function? Perhaps I am wrong and it does its PREPARE work when the function is parsed. IIRC, PLpgSQL automagically prepares each statement behind the scenes on the first use. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(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
[SQL] Dynamic prepare possible in plpgsql?
Is it possible to have a dynamic PREPARE statement in plpgsql? Something like PREPARE users_plan ( || 'text, text' || ) AS INSERT INTO pp_users( || 'col1, col2' || ) VALUES($1, $2); Regards, Collin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Dynamic prepare possible in plpgsql?
Collin Peters [EMAIL PROTECTED] writes: Is it possible to have a dynamic PREPARE statement in plpgsql? Well, you could use plpgsql's EXECUTE to run SQL PREPARE and EXECUTE commands, but it seems awfully brute-force. What do you really need to accomplish here? regards, tom lane ---(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 left join based on previous joins result
On 9/7/06, Emi Lu [EMAIL PROTECTED] wrote: I tried the example as the following:create table a(col1);create table b(col1, col2)select a.*from a inner join b using(col2)left join b.col2 as c on (c.col1 = a.col1)System notifies me that b is not a schema name. So, I guess the approach that I tried to do is not acceptable by Pgsqlgrammar.the syntax isLEFT JOIN [table] AS ...you have b.col2 which means the database will interpret col2 as the table name and subsequently b as the schema name You should have SELECT a.*FROM aINNER JOIN b using(col2)LEFT JOIN b as c on (c.col1 = a.col1)In the using(col2), what columns and tables are you joining there? I always dislike that syntax as it is ambiguous in some cases and not very easy to read. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
[SQL] Is it possible to left join based on previous joins result
Hello, Is it possible to do something like: select ... from t1 inner join t2 ... left join t2.colN When t1 inner join with t2 I got unique result for t2.colN( colN's value is table name). Can I continue to left join with the column colN in table t2? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is it possible to left join based on previous joins result
I tried the example as the following: create table a(col1); create table b(col1, col2) select a.* from a inner join b using(col2) left join b.col2 as c on (c.col1 = a.col1) System notifies me that b is not a schema name. So, I guess the approach that I tried to do is not acceptable by Pgsql grammar. Is it possible to do something like: select ... from t1 inner join t2 ... left join t2.colN When t1 inner join with t2 I got unique result for t2.colN( colN's value is table name). Can I continue to left join with the column colN in table t2? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Is it possible to left join based on previous joins result
On Thu, 7 Sep 2006, Emi Lu wrote: Hello, Is it possible to do something like: select ... from t1 inner join t2 ... left join t2.colN When t1 inner join with t2 I got unique result for t2.colN( colN's value is table name). Can I continue to left join with the column colN in table t2? No, in part because it'd have to actually evaluate the first join in order to even plan the remainder of the query. It might be possible to do something similar, albeit somewhat slowly, inside a set returning function, but you'd have to decide how to handle more than one row being returned from the first join even if the value is unique, is that one join against the table or multiple joins. ---(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 left join based on previous joins
On fim, 2006-09-07 at 10:02 -0400, Emi Lu wrote:
I tried the example as the following:
create table a(col1);
create table b(col1, col2)
select a.*
from a inner join b using(col2)
left join b.col2 as c on (c.col1 = a.col1)
System notifies me that b is not a schema name.
So, I guess the approach that I tried to do is not acceptable by Pgsql
grammar.
it seems to me that basically you want to
select from a table whose name is stored in another
table.
one way to do that would be to use plpgsql or
some other procedural language to create
a set returning function, so that you
could do:
SELECT * from selfromtab('sometable');
after that is done,you might be able to use that
in joins
gnari
Is it possible to do something like:
select ...
from t1
inner join t2 ...
left join t2.colN
When t1 inner join with t2 I got unique result for t2.colN( colN's value
is table name).
Can I continue to left join with the column colN in table t2?
Thanks
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(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?
I don't know what do you mean... because if you want to insert to other table.. you can create a rule like this CREATE RULE myrule AS ON INSERT TO mytable DO INSTEAD INSERT INTO myothertable VALUES (NEW.col1, NEW.col2, NEW.col3); that will work for all inserts.. I think the problem will be the SELECT, right?... well.. that's solved if mytable is a view of SELECT * FROM myothertable.. Juan C. Olivares www.juancri.com ---(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?
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] 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] 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
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
[SQL] Is it possible to redirect an update/insert/delete to a different table?
Hello, Is there a way that I can specify that a request to change to a particular table is redirected instead to a different table? Ive looked through rules, and as far as I can make out, they are only useful for explicit actions. Im 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. Thanks Andy Ballingall
Re: [SQL] Is it possible to set a NOT NULL constraint deferrable?
Olivier Hubaut wrote: I can put all the other constaints deferrable, but the *NOT NULL* one seems to be undeferrable. Is ther a way to by-pass this or is do you know if this is planned in the future versions? Only foreign key constraints are deferrable. Many want UNIQUE to be deferrable, but you are the first to ask for NOT NULL. Not sure when this will be done. Sorry. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Is it possible to set a NOT NULL constraint deferrable?
I can put all the other constaints deferrable, but the *NOT NULL* one seems to be undeferrable. Is ther a way to by-pass this or is do you know if this is planned in the future versions? -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Is it possible to connect to another database
Hi, I try to find how is it possible to connect 2 databases, with a symbolic link. I have to use tables in another database to test user or other information. Ben ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Is it possible to connect to another database
Take a look at dblink in the contrib directory... This may do what you need adam Hi, I try to find how is it possible to connect 2 databases, with a symbolic link. I have to use tables in another database to test user or other information. Ben ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(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] Is it possible to connect to another database
$db_conn1 = pg_connect(dbname=db1); $db_conn2 = pg_connect(dbname=db2); . You can't join two tables from different databases(, as far as i know :). - Original Message - From: BenLaKnet [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 5:03 PM Subject: [SQL] Is it possible to connect to another database Hi, I try to find how is it possible to connect 2 databases, with a symbolic link. I have to use tables in another database to test user or other information. Ben ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Is it possible to select encoding in PLPGSQL function?
Is it possible? Thank you! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] [SQL/BUGS] (possible) inconsistent behaviour of timestamp?
Here's an interesting timestamp related postgreSQL quirk:
testdb2=# select timestamp('now');
timestamp
2002-11-26 13:47:12.454157
(1 row)
testdb2=# select 'now'::timestamp;
timestamptz
--
2002-11-26 13:47:34.88358+00
(1 row)
testdb2=# select timestamp 'now';
timestamptz
---
2002-11-26 13:47:47.701731+00
(1 row)
The first SELECT returns a 'timestamp', but the next two return a
'timestamptz' (presumably with timezone); is this inconsitent behaviour?
Cheers,
Stuart.
Royal Veterinary College
London, UK
---(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] [SQL/BUGS] (possible) inconsistent behaviour of timestamp?
Rison, Stuart [EMAIL PROTECTED] writes: The first SELECT returns a 'timestamp', but the next two return a 'timestamptz' (presumably with timezone); is this inconsitent behaviour? Yes. It's a transitional behavior in 7.2: timestamp without any quotes is translated by the parser to timestamptz (ie, timestamp with time zone). Quotes suppress the renaming, however. We did that deliberately for one release to ease updating, because PG's old datetime datatype corresponds to timestamptz, not timestamp-without- time-zone. As of 7.3 that renaming doesn't happen any more, and undecorated timestamp means timestamp without time zone, per spec. Confused yet? I'm not sure that this update plan was really a great idea, but we agreed to it a release or two back, and we're sticking to it... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Is it possible to use lo_write in SQL?
Hi! Is it possible to use lo_write in SQL? If so, how? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] is it possible to get the number of rows of a table?
I would like to compare the number of rows of one table and of another and use it in a query like this: SELECT * FROM WHERE number of rows of table EQUALS number of rows of table i.e. I only want get a query result if the tables have the same number of rows. Is there a function or a way to do this ? Thanks, Frederick Basically, the way to find out about the number of rows of a table is select count(*) from t1 But the select statement above looks odd to me (no offence intended). To receive a selection of certain rows of a table you have to reference at least one column of that table in the where clause like in select * from table1 t1 where t1.c1 = (select count(*) from t2) I hope this helps at least a bit. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] is it possible to get the number of rows of a table?
I would like to compare the number of rows of one table and of another and use it in a query like this: SELECT * FROM WHERE number of rows of table EQUALS number of rows of table i.e. I only want get a query result if the tables have the same number of rows. Is there a function or a way to do this ? Thanks, Frederick __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(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] is it possible to get the number of rows of a table?
This should be really easy to implement in a function yourself. And I don't think there is already something similar in pgsql. == Wei Weng Network Software Engineer KenCast Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Frederick Klauschen Sent: Wednesday, September 26, 2001 11:15 AM To: [EMAIL PROTECTED] Subject: [SQL] is it possible to get the number of rows of a table? I would like to compare the number of rows of one table and of another and use it in a query like this: SELECT * FROM WHERE number of rows of table EQUALS number of rows of table i.e. I only want get a query result if the tables have the same number of rows. Is there a function or a way to do this ? Thanks, Frederick __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger. http://im.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] is it possible to use arrays in plpgsql function??
hi, I had the same question a couple of days ago and I received some good helps. look at the archives... regards Omid From: Bhuvan A [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [SQL] is it possible to use arrays in plpgsql function?? Date: Tue, 21 Aug 2001 11:24:08 +0530 (IST) hi all, Is it possible to use arrays of any datatypes in plpgsql function? If yes, how?? thankx. == Every absurdity has a champion who will defend it. == Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] is it possible to comment each column of a table?
hi all, is it possible to comment a column of a table similar to EXTRA of mySQL. If yes then how?? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] Is it possible to defer triggers?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, is it possible to defer the execution of a after-trigger? To explain it exactly. I am logging from apache to postgres via insert-commands. I'd like to generate online-statistics in other tables. For this I want to execute a trigger when something is inserted into the access_log-table, but without slowing down the insert's to much. WKR Torsten Knodt -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7EN2uvxZktkzSmiwRAmJUAJwNDEiZMYaogiEbp6yJxWArbLxxrgCfeVM4 MEaxCRZMTNqnGj06nD/K93Q= =uHyJ -END PGP SIGNATURE- ---(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] Is this possible?
I have a table that has a serial for primary key. Is it possible to get the new available primary key right after I insert a row of new entry? Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is this possible?
On Tue, May 08, 2001 at 09:16:56PM -0400, Wei Weng wrote: I have a table that has a serial for primary key. Is it possible to get the new available primary key right after I insert a row of new entry? Yeah. Se the documentation on triggers. -Roberto -- +| http://fslc.usu.edu USU Free Software GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Data! I thought you were dead! No, Sir. I rebooted. ---(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] PL/pgSQL: possible parsing or documentation bug?
"Albert REINER" [EMAIL PROTECTED] writes:
create Function IdOfPossiblyNewAuthor(text) returns int4 as '
declare
id int4;
begin
select id into id from author where name = $1;
raise notice ''ID found: %'', id;
if id is null then
insert into author (name) values ($1);
select currval(''author_id_seq'') into id;
raise debug ''Author inserted. ID: %'', id;
end if;
return id;
end;
' language 'plpgsql' with (IsCachable);
Logically it is clear which "id" should be parsed as the variable,
which as author.id,
No, it is not so clear. Consider the following:
declare
x int4;
y int4;
begin
x := ...;
select x + f1 into y from tab1 where ...;
The intent here is clearly to find a value tab1.f1 in tab1 and then
add the local variable x to form a value for the local variable y.
In general plpgsql will try to match an unqualified name to a variable
before it will consider whether it might be a field name. If you don't
want that, qualify the field name:
select author.id into id from author where name = $1;
Feel free to submit documentation updates to make this clearer...
regards, tom lane
[SQL] PL/pgSQL: possible parsing or documentation bug?
Saluton,
plpgsql seems to get confused when using variable names that coincide
with, e.g., column names:
create Function IdOfPossiblyNewAuthor(text) returns int4 as '
declare
id int4;
begin
select id into id from author where name = $1;
raise notice ''ID found: %'', id;
if id is null then
insert into author (name) values ($1);
select currval(''author_id_seq'') into id;
raise debug ''Author inserted. ID: %'', id;
end if;
return id;
end;
' language 'plpgsql' with (IsCachable);
Logically it is clear which "id" should be parsed as the variable,
which as author.id, but I have to use a different name to make this
work. I do not see any mention on this restriction of variable names
(the full extent of which I do not know) in the documentation (7.0.2).
Albert.
--
--
Albert Reiner [EMAIL PROTECTED]
Deutsch * English * Esperanto * Latine
--
[SQL] [notion]: a possible language addition: XQL
Hi all: has anyone heard of XQL? XQL is xml sql i just was catching up on some email and noticed this nugget http://www.ibiblio.org/xql/ anyways there is a xml::xql perl module so perl could do it but the ability to spit it right out of pg would be neato
