Re: [SQL] on select rule
http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.html SELECT does not modify any rows so you can not create SELECT triggers. Rules and views are more appropriate in such cases. On 4/14/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: > am 13.04.2006, um 18:46:02 +0300 mailte Sergey Levchenko folgendes: > > Hi > > > > If I can not do insert/update action with on select rule, how can I > > execute some procedure or any sql statement on select event? > > With a TRIGGER. > > > HTH, Andreas > -- > Andreas Kretschmer(Kontakt: siehe Header) > Heynitz: 035242/47215, D1: 0160/7141639 > GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net > ===Schollglas Unternehmensgruppe=== > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] on select rule
Sergey Levchenko <[EMAIL PROTECTED]> schrieb: > http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.htmlSELECT > does not modify any rows so you can not create SELECT triggers.Rules > and views are more appropriate in such cases. On 4/14/06, A. Oh yes, i'm sorry. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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] Simple plpgsql question
On Thursday 13 April 2006 11:38 pm, "Todd Kennedy" <[EMAIL PROTECTED]>
thus communicated:
--> Hi,
-->
--> I have, what I hope to be, a simple question about plpgsql.
-->
--> I have a trigger on a table right now that updates a count everytime
--> that a new record is entered into a database (or removed).
-->
--> What I'd also like to do is have it create a new row in a different
--> table using the automatically assigned id as a reference, but I'm
--> unsure of how to obtain the id of the newly created row in the first
--> table.
-->
--> Example:
--> CREATE TABLE system_info (
--> id serial PRIMARY KEY,
--> name varchar(255),
--> value varchar(255)
--> );
--> INSERT INTO system_info (name,value) VALUES ('total_users','0');
-->
--> CREATE TABLE master (
--> id serial PRIMARY KEY,
--> name varchar(32) NOT NULL CHECK ( name <> ''),
--> UNIQUE(name)
--> );
-->
--> CREATE TABLE slave (
--> id serial PRIMARY KEY,
--> master_id integer REFERENCES master (id),
--> additional_info text
--> );
-->
--> CREATE OR REPLACE FUNCTION update_users() RETURNS trigger AS $$
--> BEGIN
--> IF TG_OP = 'DELETE' THEN
--> UPDATE system_info SET value=(value::integer)-1 WHERE name =
--> 'total_users'
--> RETURN OLD;
--> ELSEIF TG_OP = 'INSERT' THEN
--> UPDATE system_info SET value=(value::integer)+1 WHERE name =
--> 'total_users';
--> INSERT INTO slave (master_id) VALUES (THIS IS WHAT I NEED TO KNOW);
--> RETURN NEW;
--> END IF;
--> RETURN NULL;
--> END;
--> $$ LANGUAGE plpgsql;
-->
--> CREATE TRIGGER update_users AFTER INSERT OR DELETE ON master
--> FOR EACH ROW EXECUTE PROCEDURE update_users();
-->
-->
--> The part I need to know is the INSERT INTO statement in the procedure.
-->
--> Any help would be great.
-->
--> Thanks!
-->
--> ---(end of broadcast)---
--> TIP 4: Have you searched our list archives?
-->
-->http://archives.postgresql.org
-->
INSERT INTO slave (master_id) VALUES (new.id);
The buffer NEW contains all the "new" data.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Simple plpgsql question
On Apr 13 11:38, Todd Kennedy wrote:
> What I'd also like to do is have it create a new row in a different
> table using the automatically assigned id as a reference, but I'm
> unsure of how to obtain the id of the newly created row in the first
> table.
If I understand you right, you're refering to a SERIAL column with id.
If so, you can use currval() function over related SEQUENCE. Because of
INSERT/DELETE and trigger will be executed in the same session, they'll
be able to see current value of related sequence. Below is an example
about this:
BEGIN;
CREATE SEQUENCE trig_t_seq START 1;
CREATE TABLE trig_t (
id bigint NOT NULL DEFAULT nextval('trig_t_seq'),
inf int
);
CREATE FUNCTION trig_t_row_count() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
RAISE NOTICE 'Current SEQUENCE value: %', currval('trig_t_seq');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig_t_row_count_trig
AFTER INSERT
ON trig_t
FOR EACH ROW
EXECUTE PROCEDURE trig_t_row_count();
INSERT INTO trig_t (inf) VALUES (10);
INSERT INTO trig_t (inf) VALUES (20);
INSERT INTO trig_t (inf) VALUES (30);
ROLLBACK;
Regards.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] how to solve this problem
Thank you very much!!! your suggestion seems really usefull!! I will try it very soon!!! ivan Il giorno gio, 13/04/2006 alle 11.34 -0700, Steve Crawford ha scritto: > > I have a table where there are 20 columns named > > vinc1, vinc2, vinc3, vinc4, etc > > > > the values contained into each column are simply 1 or 0 (each column is > > dichotomic) > > 1 means presence > > 0 means absence > > > > I would obtain a column (new_column) containg the name of the columns, > > comma separated, where the value is = 1 > > > > for example: > > > > vinc1 vinc2 vinc3 vinc4 new_column > > 1 0 1 0 vinc1,vinc3 > > 0 0 0 1 vinc4 > > 0 1 1 1 vinc2,vinc3,vinc4 > > > > can someone help me to find the best way to obtain this result??? > > thank you very much > > Here's a brute-force method. Maybe someone else has a more elegant way. > More info on the nature of your data and what you are trying to obtain > from it would help in finding such elegance. > > select >substr( >case when vinc1 = 1 then ',vinc1' else '' || >case when vinc2 = 1 then ',vinc2' else '' || >... >case when vinc20 = 1 then ',vinc20' else '' >,2) as new_column ... > > As to the binary representation mentioned elsewhere, the idea is that > you can view vinc1-vinc20 as bits in a binary number equal to vinc1 + > 2*vinc2 + 4*vinc3 + 8*vinc4...2^19*vinc20. Whether or not this is useful > depends on what you are trying to do. > > Cheers, > Steve > -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
