Re: [SQL] Fetch an element in an array
$row = pg_fetch_array($exec, 1);
echo $row['col2'];
Regards
Omid Omoomi
>From: Bernie Huang <[EMAIL PROTECTED]>
>To: PGSQL-SQL <[EMAIL PROTECTED]>
>Subject: [SQL] Fetch an element in an array
>Date: Wed, 07 Jun 2000 17:21:52 -0700
>
>Hi,
>
>This is more of a PHP problem, but I wish if someone knows this can help
>me. How do I fetch an element in a Postgres array?
>
>I have a table like
>
>employee
>
>col1 int4
>col2 text[]
>
>now I used the php api
>
>$row = pg_fetch_array($exec, 0);
>
>which gives me the following:
>
>echo $row['col2'];
>(prints out ===> {"123-4567", "Bernie", "123-5670"} )
>
>(echo $row['col2[2]']; <=== didn't work)
>
>What can I do to extract only the "Bernie" element?
>
>Thanks.
>
>
>- Bernie
><< bernie.huang.vcf >>
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Re: [SQL] Problem with subquery in CHECK constraint.
CONSTRAINT TYPE_CD_OK CHECK ( EXISTS (SELECT 1 FROM XREF WHERE XREF_GROUP = 'CUST_TYPE' AND XREF_CD = TYPE_CD) ) > There seems to be more serious problems. > 1) The constraint is not only for the defined table but also for referenced > tables in the subquery. I don't understand what you mean -- the constraint only constrains 1 column in one table... > 2) There should be some standard lock mechanism for the range restricted > by the subquery. > > I'm suspicious that we should/could implement constraints other than > column constraints. Again, I don't fully understand what you're saying; but I have successfully implemented the constraint using a user defined function. Niall
Re: [SQL]
Michael Fork wrote: > CREATE INDEX idx_radacct_1 ON radacct (username,acctstatustype,tstamp); > CREATE INDEX idx_radacct_2 ON radacct (username,acctstatustype); > CREATE INDEX idx_radacct_3 ON radacct (username,tstamp); > CREATE INDEX idx_radacct_4 ON radacct (acctstatustype,tstamp); > CREATE INDEX idx_radacct_5 ON radacct (tstamp); > CREATE INDEX idx_radacct_6 ON radacct (acctstatustype); > CREATE INDEX idx_radacct_7 ON radacct (username); > CREATE INDEX idx_radacct_8 ON radacct (tstamp,acctstatustype); > Hmmm - not sure what that should tell us ?!? > > Michael Fork - CCNA - MCP - A+ > Network Support - Toledo Internet Access - Toledo Ohio > Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[SQL] ORDER BY in definition of views
Guys, Does anyone know if this is on the radar for 7.1? Niall -- Niall Smart email: [EMAIL PROTECTED] phone: (087) 8052390
RE: [SQL] ORDER BY in definition of views
Title: RE: [SQL] ORDER BY in definition of views Would there be a particular reason to do this? MikeA >> -Original Message- >> From: Niall Smart [mailto:[EMAIL PROTECTED]] >> Sent: 08 June 2000 14:47 >> To: [EMAIL PROTECTED] >> Subject: [SQL] ORDER BY in definition of views >> >> >> Guys, >> >> Does anyone know if this is on the radar for 7.1? >> >> Niall >> >> -- >> Niall Smart >> >> email: [EMAIL PROTECTED] >> phone: (087) 8052390 >>
Re: [SQL] ORDER BY in definition of views
Michael Ansley wrote: > > Would there be a particular reason to do this? > I like to define views for lookups (i.e. job titles, departments, accounts, whatever). It would be nice if I could just do a select * from whatever_vw instead of having to remember to put the order by whatever_name clause. Admittedly its no biggie, but it is nice-to-have. Niall
Re: [SQL] ORDER BY in definition of views
Niall Smart <[EMAIL PROTECTED]> writes: > Does anyone know if this is on the radar for 7.1? 7.2 ... regards, tom lane
[SQL] SQL 'Case When...'
Hi, Don't know if it's possible, but how do I make the following SQL statments right? table --- name int4 attribute text[] select attribute[1] from table case when attribute[1]='yes' then select attribute[2] from table else select attribute[3] from table; Basically, I want to compare attr[1], if true then select some fields from table; if false then select others. Thank you. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
[SQL] trigger/refint question..
Hi.
Im trying to implement a referential integrity check that I suspect there
is a much better way to do that what I have done. Basically what I have is
a "order" table and a "order items" table. I would like to make it so that
if all items are removed from the order, then the corresponding "order"
entry is removed.
e.g.:
CREATE TABLE orders (
id SERIAL,
dname TEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE order_items (
item_id SERIAL,
order_id INT NOT NULL,
item VARCHAR(80) NOT NULL,
PRIMARY KEY (item_id)
);
BEGIN;
INSERT INTO orders (dname) VALUES ('FOO');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'FOO ITEM #1');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'FOO ITEM #2');
INSERT INTO orders (dname) VALUES ('BAR');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'BAR ITEM #1');
INSERT INTO order_items (order_id, item)
VALUES (CURRVAL('orders_id_seq'), 'BAR ITEM #2');
COMMIT;
So we have 2 orders, each with 2 items in it. Suppose someone later comes
along and deletes all of the items in the order:
DELETE FROM order_items
WHERE order_id=1;
Ideally, I would like a trigger (or something similar) to fire after this
delete runs that does something like:
DELETE FROM orders
WHERE id IN (
SELECT id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE order_id=o.id)
);
(sort of the invers of "FOREIGN KEY (...) REFERENCES (..) ON DELETE CASCADE").
The trick seem sto be getting this into a function somehow. The delete
query does not return a value, so I am not sure how to go about doing that.
Assuming I *could* get this into an sql or plpgsql function somehow, I could
simply do:
CREATE TRIGGER tr_order_items_del AFTER DELETE ON order_items
FOR EACH ROW EXECUTE PROCEDURE del_order_items();
And I think that would solve my problem.
Am I making this overly complicated? Is there an easier way? If not,
then does anyone have any ideas how I can make this work?
Thanks.
Mike
RE: [SQL] Problem with subquery in CHECK constraint.
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Niall Smart > > > CONSTRAINT TYPE_CD_OK CHECK ( > EXISTS (SELECT 1 FROM XREF WHERE > XREF_GROUP = 'CUST_TYPE' AND > XREF_CD = TYPE_CD) > ) > > > > There seems to be more serious problems. > > 1) The constraint is not only for the defined table but also > for referenced > > tables in the subquery. > > I don't understand what you mean -- the constraint only > constrains 1 column in one table... > Doesn't the constraint mean that for any row in table CUST,there *always* exist some rows in the table XREF such that satisfies XREF_GROUP='CUST_TYPE' AND XREF_CD=TYPE_CD ? If all such rows are deleted from the table XREF,above condition isn't satisfied any longer. So isn't the constraint for the table XREF either ? Regards. Hiroshi Inoue [EMAIL PROTECTED]
Re: [SQL] Problem with subquery in CHECK constraint.
> CONSTRAINT TYPE_CD_OK CHECK (
> EXISTS (SELECT 1 FROM XREF WHERE
> XREF_GROUP = 'CUST_TYPE' AND
> XREF_CD = TYPE_CD)
> )
>
>
> > There seems to be more serious problems.
> > 1) The constraint is not only for the defined table but also for
referenced
> > tables in the subquery.
>
> I don't understand what you mean -- the constraint only
> constrains 1 column in one table...
Think of this sequence:
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '1');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '2');
insert into xref (xref_group, xref_cd) values ('CUST_TYPE', '3');
insert into cust (cust_id, name, type_cd) values (1, 'a', '1');
insert into cust (cust_id, name, type_cd) values (2, 'b', '2');
delete from xref where xref_cd='1';
I believe that technically the delete should fail because it breaks the
type_cd_ok constraint, but right now, we wouldn't be even checking
that constraint to notice that we're breaking it.
[SQL] Sum of datetime différence...
Hi, I would like to know what is the easiest way to add multiple difference of datetime but show the result in hour and minute something like : table foo: start datetime end datetime I would like to do : select sum(end-start) from foo; but it gives me 3 days 12 hours 23 minutes...etc I would like to see 84 hours and 23 minutes... How can I do this? PS: Is there a way to know which week of the year a current day is in? Frédéric Boucher [EMAIL PROTECTED]
[SQL] Help with inserts into Views
Greetings,
I am having difficulty creating a way to insert into a view which joins
multiple tables (specifically a view which joins multiple records from a
single table to a record in another table).
Please see the enclosed simple example for how I am currently inserting
(updating, etc.) on views. However, When I get more complex tables with
multiple joins, this method will not work because a plpgsql function can
accept only 16 arguments. I have tried using the NEW variable as the
argument to the function; however, this does not seem to work.
No matter what I do to create an insert trigger on the view, it never seems
to fire the trigger.
Any help and suggestions on how to perform an insert into multiple tables
from a single joined view would be greatly appreciated.
Thank you,
Brian
Example:
drop sequence addr_id_seq;
drop sequence member_id_seq;
drop rule v_member_insert;
drop function member_insert(varchar, varchar, varchar, varchar, varchar,
varchar, varchar);
drop view v_member;
drop table member;
drop table addr;
create sequence addr_id_seq;
create table addr (
addr_idint4 primary key default nextval('addr_id_seq'),
street varchar(40) not null,
city varchar(40) not null,
state varchar(40) not null
);
create sequence member_id_seq;
create table member (
member_idint4 primary key default nextval('addr_id_seq'),
username varchar(40) not null,
address_id int4 not null,
shipping_id int4 not null
);
create view v_member as
select m.member_id, m.username, a.street, a.city, a.state,
s.street as ship_street, s.city as ship_city,
s.state as ship_state
from member m, addr a, addr s
where m.address_id = a.addr_id and m.shipping_id = s.addr_id;
create function member_insert(varchar, varchar, varchar, varchar,
varchar, varchar, varchar) returns text as '
declare
my_address_id integer;
my_shipping_id integer;
my_username ALIAS FOR $1;
my_street ALIAS FOR $2;
my_city ALIAS FOR $3;
my_state ALIAS FOR $4;
my_ship_street ALIAS FOR $5;
my_ship_city ALIAS FOR $6;
my_ship_state ALIAS FOR $7;
begin
my_address_id := nextval(''addr_id_seq'');
insert into addr
(addr_id, street, city, state)
values (my_address_id, my_street, my_city, my_state);
my_shipping_id := nextval(''addr_id_seq'');
insert into addr
(addr_id, street, city, state)
values (my_shipping_id, my_ship_street, my_ship_city,
my_ship_state);
insert into member (username, address_id, shipping_id)
values (my_username, my_address_id, my_shipping_id);
return ''Success'';
end;
' language 'plpgsql';
CREATE RULE v_member_insert AS
ON INSERT TO v_member
DO INSTEAD
SELECT member_insert(new.username, new.street, new.city, new.state,
new.ship_street, new.ship_city, new.ship_state);
-- Should create an error
insert into v_member (username) values ('bob');
-- Should create a record
insert into v_member
(username, street, city, state, ship_street, ship_city, ship_state)
values ('bob', '123 Main', 'Denver', 'CO', '543 Elm', 'Buttland',
'MS');
select * from member;
select * from addr;
select * from v_member;
[SQL] Is it possible to "truncate" a LOB?
[I asked this question on the "interfaces" list, but nobody answered ...] Suppose that I have created a LOB, written a bunch of data to it, and committed. Is it now possible to rewrite the LOB to contain a lesser amount of data? In other words can I do the equivalent of UNIX open(..., O_WRONLY | O_TRUNC) or ftruncate(...) on a LOB? Or am I forced to delete the existing LOB and create a new one if I want to shrink the size of the stored data? -- Steve
