Re: [SQL] Update and trigger
Thanks...but a difference seems to be that the rule is not specific to
update on a particular col but any col of a row getting updated...
Thanks
On Tue, Jun 10, 2008 at 10:21 PM, A. Kretschmer <
[EMAIL PROTECTED]> wrote:
> am Tue, dem 10.06.2008, um 18:45:51 -0700 mailte Medi Montaseri folgendes:
> > Hi,
> >
> > I need to increament a counter such as myTable.Counter of type integer
> > everytime myTable.status a boolean column is updated. Can you help me
> complete
> > this...
> >
> > create trigger counter_trigger after update on myTable.counter
> > execute procedure 'BEGIN statement; statement; statement END'
>
> much simpler, use a RULE instead a TRIGGER like my example:
>
> Suppose, i have a table called foo, it contains now:
>
> test=# select * from foo;
> i
> ---
> 1
> 2
> (2 rows)
>
>
> I create a sequence and a RULE:
>
> test=*# create sequence foo_counter;
> CREATE SEQUENCE
> test=*# create or replace rule foo_update as on update to foo do also
> select nextval('foo_counter');
> CREATE RULE
>
>
> And now i do a update on foo:
>
>
> test=*# update foo set i=2;
> nextval
> -
> 1
> (1 row)
>
> test=*# update foo set i=3;
> nextval
> -
> 2
> (1 row)
>
>
> test=*# select currval('foo_counter');
> currval
> -
> 2
> (1 row)
>
>
>
> 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
>
Re: [SQL] Update and trigger
am Wed, dem 11.06.2008, um 0:54:55 -0700 mailte Medi Montaseri folgendes: > Thanks...but a difference seems to be that the rule is not specific to update > on a particular col but any col of a row getting updated... > > Thanks Okay, but i havn't an idea. A statement level trigger can't see the the data in NEW and OLD. And a row level trigger fires for every row. > > On Tue, Jun 10, 2008 at 10:21 PM, A. Kretschmer < > [EMAIL PROTECTED]> wrote: Please, no silly top posting with fullquote below, i'm reading from top to bottom. 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] Different type of query
I have a table like this: usda=# \d nutrient_data Table "public.nutrient_data" Column | Type | Modifiers -+---+--- ndb_no | integer | not null nutrient_no | integer | not null nutrient_value | double precision | not null data_points | double precision | not null std_error | double precision | src_cd | integer | not null derivation_code | character varying(5) | ref_ndb_no | integer | add_nutr_mark | character varying(2) | num_studies | integer | min | double precision | max | double precision | df | numeric | low_eb | double precision | up_eb | double precision | stat_cmt| character varying(15) | cc | character varying(5) | Indexes: "nutrient_data_pkey" PRIMARY KEY, btree (ndb_no, nutrient_no) Foreign-key constraints: "nutrient_data_derivation_code_fkey" FOREIGN KEY (derivation_code) REFERENCES derivation_code(derivation_code) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_ndb_no_fkey" FOREIGN KEY (ndb_no) REFERENCES food_description(ndb_no) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_nutrient_no_fkey" FOREIGN KEY (nutrient_no) REFERENCES nutrient_definitions(nutrient_no) ON UPDATE CASCADE ON DELETE CASCADE "nutrient_data_src_cd_fkey" FOREIGN KEY (src_cd) REFERENCES source_code(src_cd) ON UPDATE CASCADE ON DELETE CASCADE when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 13473 | 255 | 57.78 13473 | 262 | 0 13473 | 263 | 0 13473 | 268 | 1033 13473 | 269 | 0 13473 | 291 | 0 13473 | 301 | 5 13473 | 303 | 3.35 13473 | 304 | 24 13473 | 305 |199 13473 | 306 |302 13473 | 307 | 67 13473 | 309 | 4.67 13473 | 312 | 0.131 13473 | 315 | 0.015 13473 | 317 | 10.9 13473 | 318 | 0 13473 | 319 | 0 13473 | 320 | 0 13473 | 321 | 0 13473 | 322 | 0 13473 | 323 | 0.18 13473 | 334 | 0 13473 | 337 | 0 13473 | 338 | 0 13473 | 401 | 0 13473 | 404 | 0.101 I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. how would I write that query. BIG THANKS in advance as I'm lost on this one. J -- 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] Different type of query
PostgreSQL Admin wrote: I have a table ... when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 ... I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. Not entirely sure I understand the question. Do you mean that for a given nutrient_no, you want the complete list of nutrient values? If so, it's just: --Example for nutrient_no 203: SELECT ndb_no, nutrient_value from nutrient_data where nutrient_no=203; Cheers, Steve -- 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] Different type of query
Steve Crawford wrote: PostgreSQL Admin wrote: I have a table ... when I run this query: select ndb_no, nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473; it produces: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 ... I want only certain nutrient_no (say 8 of them) and the nutrient values by ndb_no. Not entirely sure I understand the question. Do you mean that for a given nutrient_no, you want the complete list of nutrient values? If so, it's just: --Example for nutrient_no 203: SELECT ndb_no, nutrient_value from nutrient_data where nutrient_no=203; Cheers, Steve I would like to have multiple values nutrient_no: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 I'm thinking: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208); Now is that the most efficient SQL query? Thanks, J -- 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] Different type of query
On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote: > I would like to have multiple values nutrient_no: > ndb_no | nutrient_no | nutrient_value > +-+ > 13473 | 203 | 24.18 > 13473 | 204 | 15.93 > 13473 | 205 | 0 > 13473 | 207 |1.1 > 13473 | 208 |247 > 13473 | 221 | 0 > > I'm thinking: > select nutrient_no, nutrient_value from nutrient_data where ndb_no = > 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = > 208); > > > Now is that the most efficient SQL query? > > Thanks, > J It seems that you'd want to do something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and nutrient_no in (203, 204, 208..) You could also grab the most significant 8 nutrients by doing something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 order by nutrient_value desc limit 8 -Mark -- 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] Different type of query
Mark Roberts wrote: On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote: I would like to have multiple values nutrient_no: ndb_no | nutrient_no | nutrient_value +-+ 13473 | 203 | 24.18 13473 | 204 | 15.93 13473 | 205 | 0 13473 | 207 |1.1 13473 | 208 |247 13473 | 221 | 0 I'm thinking: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208); Now is that the most efficient SQL query? Thanks, J It seems that you'd want to do something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 and nutrient_no in (203, 204, 208..) You could also grab the most significant 8 nutrients by doing something like: select nutrient_no, nutrient_value from nutrient_data where ndb_no = 13473 order by nutrient_value desc limit 8 -Mark Thanks Mark! -- 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] Different type of query
I would like to have multiple values nutrient_no:
ndb_no | nutrient_no | nutrient_value
+-+
13473 | 203 | 24.18
13473 | 204 | 15.93
13473 | 205 | 0
13473 | 207 |1.1
13473 | 208 |247
13473 | 221 | 0
I'm thinking:
select nutrient_no, nutrient_value from nutrient_data where ndb_no =
13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no =
208);
Depending on what you are trying to achieve:
Particular ndb_no and multiple nutrient_no, note that output of ndb_no
is superfluous as it is always the same:
select nutrient_no, nutrient_value from nutrient_data where ndb_no =
13473 and nutrient_no in ('203', '204','208');
Size limited list (say top 5 nutrient values) for a given ndb_no:
select nutrient_no,nutrient_value from nutrient_data where ndb_no =
13473 order by nutrient_value limit 5;
Cheers,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
