Re: [SQL] Multi-row update w. plpgsql function
Hi, If your checkboxes are like input type="checkbox" name="approved" value="1" input type="checkbox" name="approved" value="2" input type="checkbox" name="approved" value="3" input type="checkbox" name="approved" value="4" and 1, 3 and 4 are checked your form data will be approved=1,3,4 Then you can just say UPDATE message_table SET status = 'A' WHERE mid IN($approved); Not a function though. BR, Aarni testing=# SELECT * FROM message_table; mid | message | status -+-+ 1 | text1 | H 2 | text2 | H 3 | text3 | H 4 | text4 | H (4 rows) testing=# UPDATE message_table SET status = 'A' WHERE mid IN(1,3,4); UPDATE 3 testing=# SELECT * FROM message_table; mid | message | status -+-+ 2 | text2 | H 1 | text1 | A 3 | text3 | A 4 | text4 | A (4 rows) testing=# On Wednesday 14 December 2005 01:00, Daniel Hertz wrote: > Given a set of checkbox values that are submitted through an html form, > how do you loop through the submitted values to update more than one row > in a table? > > Imagine a table called 'message_table': > > mid | message | status > +-+--- > 1 | Text1 | H > 2 | Text2 | H > 3 | Text3 | H > 4 | Text4 | H > > A web page presents the user with all messages flagged with 'H'. User > checks messages 1,3 and 4 and submits form. > (i.e. approved=1&approved=3&approved=4) > > After performing postgreSQL update, rows 1, 3 and 4 would be updated to: > > mid | message | status > +-+--- > 1 | Text1 | A > 2 | Text2 | H > 3 | Text3 | A > 4 | Text4 | A > > I have never written a plpgsql function, but tried: > > CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS > integer AS > $body$ > DECLARE > new_status varchar; > new_sample record; > > BEGIN > new_status := 'A'; > > FOR new_sample IN SELECT * FROM message_table WHERE status='H' ORDER BY > mid LOOP > UPDATE message_table SET status = new_status > WHERE mid = approved; > END LOOP; > > RETURN 1; > END; > $body$ > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; > > I call the function with: > SELECT update_messages(); > > I'm using apache cocoon, which is why you see the variable placeholder: > ); > > Unfortunately, the function only updates the first value submitted (mid > 1), and doesn't loop through the other two values submitted. > > Can someone help this novice from getting ulcers? > > Thanks for your help! > > Daniel > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Aarni Ruuhimäki -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core** linux system -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Multi-row update w. plpgsql function
> > Imagine a table called 'message_table': > > > > mid | message | status > > +-+--- > > 1 | Text1 | H > > 2 | Text2 | H > > 3 | Text3 | H > > 4 | Text4 | H > > > > A web page presents the user with all messages flagged with > 'H'. User > > checks messages 1,3 and 4 and submits form. > > (i.e. approved=1&approved=3&approved=4) > > > > After performing postgreSQL update, rows 1, 3 and 4 would > be updated > > to: > > > > mid | message | status > > +-+--- > > 1 | Text1 | A > > 2 | Text2 | H > > 3 | Text3 | A > > 4 | Text4 | A > > BEGIN; > UPDATE message_table SET status = 'A' WHERE mid = 1; UPDATE > message_table SET status = 'A' WHERE mid = 3; UPDATE > message_table SET status = 'A' WHERE mid = 4; COMMIT; > > would do that. Have your application generate an appropriate > UPDATE line for each "approved" entry in the form data, wrap > it in a transaction, and away you go. It would probably be even more efficient to do: UPDATE message_table SET status = 'A' WHERE mid IN (1,3,4) and then use client code to generate the comma-separated list of ids. (Don't forget to make sure they are actual integers so you don't get a sql injection from it - I don't think parametrised queries can deal with comma lists) //Magnus ---(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] DB design and foreign keys
[cut] order_code is not by itself unique --- SERIAL doesn't guarantee that. that was my misunderstanding, i thought (misunderstood) that 'serial' implied 'unique' I'm not sure why you are declaring the primary key of orders as being the combination of *two* serial columns, i thought it was good design choice and even needed for foreign keys referencing on them but if that's what you really need i'm not shure about that and you also want to be able to reference a row by just one of them, you'll need to apply a separate unique constraint to just the order_code column. sorry, i'm afraid i didn't understand: are you suggesting to apply the constraint in case the primary key is kept on the combination of the two serial columns? or to remove the primary key of the two serial columns? regards, tom lane thanks a lot for your kind response, best regards, Gianluca Riccardi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DB design and foreign keys
John McCawley wrote: Table orders defines the column order_code as a serial, which simple makes a trigger which gives a new value to the column on insert. Note that there is NO guarantee that ths column will be unique. You can manually update the value to whatever you want. If you wish this column to be unique, you must specify it on creation, or later do an alter table add constraint to the column. A foreign key requires that the referenced column be unique (DB enforced, not just coincidentally unique), and that' s why your table creation is failing. [cut] that was my misunderstanding thanks for your reply, Gianluca Riccardi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DB design and foreign keys
Jaime Casanova wrote: [...unnecesary...] CREATE TABLE orders ( id serial, order_code serial, customer_code integer REFERENCES customers (customer_code) NOT NULL, order_date time without time zone NOT NULL, remote_ip inet NOT NULL, order_time timestamp with time zone NOT NULL, order_type varchar(10) NOT NULL, state varchar(10) NOT NULL, PRIMARY KEY (id, order_code) ^^^ ); [...unnecesary...] CREATE TABLE order_items ( id serial, order_code integer REFERENCES orders (order_code) NOT NULL, ^^ [...unnecesary...] ERROR: there is no unique constraint matching given keys for referenced table "orders" this is because the PK in the orders table has two fields not one... so it founds no unique index on orders(order_code) then, what would you suggest, to remove the primary key from the 'id' column? or to remove the 'id' column at all? -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) thank you for your response, regards, Gianluca Riccardi ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DB design and foreign keys
[cut] It means what it says. You have defined table orders with a primary key of (id,order_code). This means that the combination of (id,order_code) must be unique. yes, that was my thought, and in that context, i thought it could be correct in order to have uniqueness for creating foreign keys So - these could all exist at the same time: (1,1), (1,2), (2,1), (2,2) You could not then add another (1,2) combination. yes, again, i thought that was the uniqueness i needed... Since id and order_code are both just automatically-generated numbers in the orders table it doesn't add anything to make both of them part of a primary-key. I would delete the id column altogether and just have the order_code as the primary-key (since "order_code" carries more meaning to a human than "id"). This means your order_items table can then safely reference the order_code it wants to. HTH sure it did, thanks for your response, best regards, Gianluca Riccardi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DB design and foreign keys
[cut] Given this table layout, I'm gonna take a wild guess and ask if you're coming from MySQL and expecting the second serial order_code to be a sub-autoincrement to id? no, always used PostgreSQL, but i'm having a deeper approach now, until now i've been using th ORDBMS in a very 'easy' manner :( If so, it won't be. That's a mysqlism. If you want something similar, you'll have to implement it yourself, and note that such a thing tends to be a poor performer with lots of parallel updates, and it can also be susceptible to race conditions if no locking is used. thank you, regards, Gianluca Riccardi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] DB design and foreign keys
so, after the needed modifications the SQL schema is the following -- SQL schema for business-test-db2 CREATE TABLE customers ( customer_code serial UNIQUE, alfa_customer_code varchar(6), customer_name character varying(250) NOT NULL, address character varying(250) NOT NULL, city character varying(250) NOT NULL, zip_code character varying(8) NOT NULL, prov character varying(30) NOT NULL, security character varying(15) NOT NULL, tel character varying(30), tel2 character varying(20) NOT NULL, fax character varying(250), url character varying(250), email1 character varying(250) NOT NULL, email2 character varying(250) NOT NULL, discount1 integer, discount2 integer, PRIMARY KEY (customer_code) ); CREATE TABLE users ( id smallint NOT NULL, login varchar(20) NOT NULL, pwd varchar(20) NOT NULL, name varchar(20) NOT NULL, customer_code int REFERENCES customers (customer_code), valid date, primary key (id) ); CREATE TABLE products ( code varchar(60) UNIQUE NOT NULL, description varchar(250) NOT NULL, dimensions varchar(250) NOT NULL, price numeric NOT NULL, state boolean, PRIMARY KEY (code) ); CREATE TABLE orders ( order_code serial UNIQUE NOT NULL, customer_code integer REFERENCES customers (customer_code) NOT NULL, order_date time without time zone NOT NULL, remote_ip inet NOT NULL, order_time timestamp with time zone NOT NULL, order_type varchar(10) NOT NULL, state varchar(10) NOT NULL, PRIMARY KEY (order_code) ); CREATE TABLE order_items ( order_code integer REFERENCES orders (order_code) NOT NULL, customer_code integer REFERENCES customers (customer_code) NOT NULL, product_code varchar(60) REFERENCES products (code) NOT NULL, qty int NOT NULL, price numeric NOT NULL, row_price numeric ); -- -- END OF FILE the order_items table is: business-test-db2=# \d order_items Tabella "public.order_items" Colonna | Tipo | Modificatori ---+-+-- order_code | integer | not null customer_code | integer | not null product_code | character varying(60) | not null qty | integer | not null price| numeric| not null row_price| numeric| Vincoli di integrità referenziale "$1" FOREIGN KEY (order_code) REFERENCES orders(order_code) "$2" FOREIGN KEY (customer_code) REFERENCES customers(customer_code) "$3" FOREIGN KEY (product_code) REFERENCES products(code) business-test-db2=# thanks a lot, you all pointed me out of misconceptualized position. best ragards all, Gianluca Riccardi p.s. Colonna = column Tipo = type Modificatori = modifiers Vincoli di integrita' referenziale = referential integrity constraints ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Finding out to which table a specific row belongs
Hi there, I have the following problem: My database consists of several tables that are inherited from each other with one single supertable on top of that hierarchy. Now I would like to select a single row in that supertable and want to find out to which of the tables in the hierarchy it belongs. As far as I understand the pg_depend table only shows the hierarchy of the tables but does not contain the oids of the actual rows. Is there any possibility to solve this problem by using the pg_* tables? I would very much appreciate any help! Thank you in advance, Jost ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Finding out to which table a specific row belongs
On Wed, Dec 14, 2005 at 06:26:23PM +0100, Jost Degenhardt wrote:
> I have the following problem: My database consists of several tables
> that are inherited from each other with one single supertable on top of
> that hierarchy. Now I would like to select a single row in that
> supertable and want to find out to which of the tables in the hierarchy
> it belongs.
http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html
In some cases you may wish to know which table a particular row
originated from. There is a system column called tableoid in
each table which can tell you the originating table:
http://www.postgresql.org/docs/8.1/interactive/ddl-system-columns.html
tableoid
The OID of the table containing this row. This column is particularly
handy for queries that select from inheritance hierarchies (see
Section 5.8), since without it, it's difficult to tell which
individual table a row came from. The tableoid can be joined
against the oid column of pg_class to obtain the table name.
Here's an example; instead of getting the table name via a join
with pg_class it uses a cast to regclass:
CREATE TABLE parent (t text);
CREATE TABLE child1 () INHERITS (parent);
CREATE TABLE child2 () INHERITS (parent);
INSERT INTO child1 VALUES ('one');
INSERT INTO child2 VALUES ('two');
SELECT tableoid::regclass, * FROM parent;
tableoid | t
--+-
child1 | one
child2 | two
(2 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Defaulting a column to 'now'
How can a column’s default be set to ‘now’, meaning ‘now’ as of when each row is inserted? For example, here’s a snip of DDL: create table personal_data (… effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 'now',… The problem is, when PostgreSQL processes this DDL, it interprets the ‘now’ as the timestamp when the table is created, so that the tables definition reads as if the DDL were: effective_date_and_time TIMESTAMP WITH TIME ZONE not null default ' 2005-12-14 11:00:16.749616-06 ', so all of the newly inserted rows get assigned effective_date_and_time = ' 2005-12-14 11:00:16.749616-06 ', which in addition to being wrong leads to uniqueness constraint violations. ~ TIA ~ Ken
Re: [SQL] Defaulting a column to 'now'
"Ken Winter" <[EMAIL PROTECTED]> writes: > How can a column's default be set to 'now', meaning 'now' as of when each > row is inserted? You need a function, not a literal constant. The SQL-spec way is CURRENT_TIMESTAMP (which is a function, despite the spec's weird idea that it should be spelled without parentheses); the traditional Postgres way is now() Either way only sets an insertion default, though. If you want to enforce a correct value on insertion, or change the value when the row is UPDATEd, you need to use a trigger. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Defaulting a column to 'now'
Ken Winter wrote: How can a column’s default be set to ‘now’, meaning ‘now’ as of when each row is inserted? For example, here’s a snip of DDL: create table personal_data (… effective_date_and_time TIMESTAMP WITH TIME ZONE not null default 'now',… try with now(), instead of now ... effective_date_and_time TIMESTAMPTZ not null default now()... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Defaulting a column to 'now'
On Wed, 14 Dec 2005 13:10:50 -0500, Ken Winter wrote
> How can a column's default be set to 'now', meaning 'now' as of when each
> row is inserted?
>
> For example, here's a snip of DDL:
>
> create table personal_data (.
>
> effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
> 'now',.
>
> The problem is, when PostgreSQL processes this DDL, it interprets the 'now'
> as the timestamp when the table is created, so that the tables definition
> reads as if the DDL were:
>
> effective_date_and_time TIMESTAMP WITH TIME ZONE not null default '
> 2005-12-14 11:00:16.749616-06 ',
>
> so all of the newly inserted rows get assigned effective_date_and_time
> = '
> 2005-12-14 11:00:16.749616-06 ', which in addition to being wrong
> leads to uniqueness constraint violations.
>
> ~ TIA
>
> ~ Ken
Ken,
effective_date_and_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
('now'::text)::timestamp(6) with time zone
Kind Regards,
Keith
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] # of 5 minute intervals in period of time ...
On Tue, Dec 13, 2005 at 18:34:36 -0400,
"Marc G. Fournier" <[EMAIL PROTECTED]> wrote:
>
> Is there a simpler way of doing this then:
>
> select (date_part('epoch', now()) -
> date_part('epoch', now() - '30 days'::interval)) / ( 5 * 60 );
Are you trying to do this:
select extract(epoch from '30 days'::interval) / 300;
The above works in 8.0, but I don't know for sure what it does in 8.1.
Probably it just treats days as 24 hours, similar to how months are treated
as having 30 days.
>
>
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Multi-row update w. plpgsql function
Aaron Koning wrote: Owen makes a good point. Check that you are using the [] in the HTML input variable for the checkboxes. Like: 1 2 3 4 Aaron On 12/13/05, Owen Jacobson <[EMAIL PROTECTED]> wr I'm not familiar with Cocoon, but I'd expect that to return only the first of the "approved" values from the HTTP request. If you add logging to the stored function (RAISE NOTICE 'approved: %', approved; near the start of the function, for instance) and tell PostgreSQL to store the logs, you can see what values your function is actually being called with. What you really want to do is begin a transaction, loop over all the values of approved present in the form data and call (the rewritten version of) update_messages for each one, then commit the transaction. -Owen Thank you all, so much, for taking the time to help me out. Especially as a beginner, where coding IS rocket science. On the general board, Aaron mentioned: UPDATE message_table SET status = 'A' WHERE mid IN (1,2,3); which seems very succinct and economical. I'm gonna have a go at parsing the query string using XSLT, substituting the variable for: UPDATE message_table SET status = 'A' WHERE mid IN ($query_values_here); Again, thanks for the help, Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Extract date from week
Jaime Casanova wrote:
On 11/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
Hi
Looking the e-mail I remembered a question.
I saw that "select extract (week from now()::date)" will return the
week number
of current year. But, how can I convert a week to the first reference
date. Ex:
select extract(week from '20050105'::date); -- 5 Jan 2005
--Returns--
date_part |
1 |
It is the first week of year (2005), and how can I get what is the first date
references the week 1? Ex:
select week 1
--should return---
date |
20050103 | -- 3 Jan 2005
Thank you.
Lucas Vendramin
Extracted from:
http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
--- begin extracted text ---
week
The number of the week of the year that the day is in. By definition
(ISO 8601), the first week of a year contains January 4 of that year.
(The ISO-8601 week starts on Monday.) In other words, the first
Thursday of a year is in week 1 of that year. (for timestamp values
only)
Because of this, it is possible for early January dates to be part of
the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
week of year 2005.
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
--- end extracted text ---
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Hi
http://www.postgresql.org/docs/8.0/static/functions-formatting.html
for the first week of 2005 use
=> select to_timestamp('1 2005','IW ')::date as week_start;
week_start
2005-01-03
Regards Neil.
---(end of broadcast)---
TIP 6: explain analyze is your friend
