[SQL] Need Help for select
Hi all, i need help to build a select query or plpgsql-fucntion for the following tables. create table a ( id int, name varchar(20) ) create table b ( a_id int, c_id int ) create table c ( b_id int, d_id int ) create table d ( id int, name varchar(20) ) Is it possible to build a select query that selects d.name for each a.name where a.id = b.a_id and d.id = c.d_id and each b.c_id must exist in c.b_id. Example: a:b: c : d: id | name a_id | c_idb_id | d_idid | name |--- ---|- ---|- -| 1 | A_Name11 | 1 1 | 1 1 | D_Name1 2 | A_Name21 | 2 2 | 1 2 | D_Name2 3 | A_Name32 | 1 3 | 2 3 | D_Name3 4 | A_Name43 | 3 4 | 2 3 | 4 5 | 3 4 | 5 i wish to have to following result: | A_Name1 | D_Name1 A_Name3 | D_Name2 A_Name4 | D_Name3 I hope someone could understand the problem Thanks in advance and sorry for my bad english ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Calling stored procedures in table constraint checks
I guess I need an example how I can pass an entire row to a stored procedure called in a table constraint check. Is this possible at all? -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Calling stored procedures in table constraint checks
Florian Weimer <[EMAIL PROTECTED]> writes: > I guess I need an example how I can pass an entire row to a stored > procedure called in a table constraint check. > Is this possible at all? In CVS tip it works to do this: regression=# create function foo(tenk1) returns int as ' regression'# begin regression'# return $1.unique2; regression'# end' language plpgsql; CREATE -- min(unique2) is 0, so: regression=# alter table tenk1 add constraint c2 check (foo(tenk1.*) > 0); ERROR: AlterTableAddConstraint: rejected due to CHECK constraint c2 regression=# alter table tenk1 add constraint c2 check (foo(tenk1.*) >= 0); ALTER TABLE The older syntax also works: regression=# alter table tenk1 add constraint c3 check (foo(tenk1) >= 0); ALTER TABLE The latter *ought* to work in 7.2, but seems not to --- it looks like the thing runs through the ALTER TABLE check, and then fails at the last moment where it's trying to re-parse the expression for storage. Grumble. In any case this is a bit of a mess, because you can't create the function until the row type exists, so you have to do it as create table, create function, alter table add constraint. That's not only ugly but will confuse the heck out of pg_dump. (thinks...) It might work better to create a parent table, create the function taking the parent's rowtype, then define the table you care about as inheriting from the parent with no added columns and having the desired constraint. In any case you'll probably have to wait for 7.3. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Need Help for select
--- Andre Schubert <[EMAIL PROTECTED]> wrote: > Hi all, > > i need help to build a select query or > plpgsql-fucntion > for the following tables. >> Is it possible to build a select query that selects > d.name for each a.name where > a.id = b.a_id and d.id = c.d_id and each b.c_id must > exist in c.b_id. > > Example: > a: b: c : d: > id | name a_id | c_idb_id | d_idid | > name > |--- ---|- ---|- > -| > 1 | A_Name11 | 1 1 | 1 1 | > D_Name1 > 2 | A_Name21 | 2 2 | 1 2 | > D_Name2 > 3 | A_Name32 | 1 3 | 2 3 | > D_Name3 > 4 | A_Name43 | 3 4 | 2 > 3 | 4 5 | 3 >4 | 5 > > i wish to have to following result: > | > A_Name1 | D_Name1 > A_Name3 | D_Name2 > A_Name4 | D_Name3 > > I hope someone could understand the problem You can use views to to simplify complicated queries Create a view that will join table A & B Create view view_ab(name,id) as select name,c_id from a,b where id = c_id; Create a view that will join table C & D Create view view_cd(name2,id2) as select name,b_id from c,d where id=d_id; Create a query that will join the views "view_ab" and "view_cd" Select name,name2 from view_ab,view_cd where id=id2; __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Is this valid?
I am not sure if this is the right mailing list I talk to. Please let me know if I had violated any unwritten rules. :) I have a global variable PGconn* m_pgconn that is the connection handle for connecting to the postgresql database. Can I access/use the handle from multiple threads? Say I have a thread that does some insertion through this handle/database connection(m_pgconn) and also another thread that do some insertion *on the same table* through this handle(m_pgconn), will that break? Thanks -- Wei Weng Network Software Engineer KenCast Inc. ---(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] Is this valid?
I'm going to ask the crazy question of what language/interface are you using to interact with postgres? Based on my interpretation of your question I'd say that won't break (though one of your queries might fail) but then again I may be totally misreading what you wrote... Robert Treat On Mon, 2002-08-12 at 19:21, Wei Weng wrote: > I am not sure if this is the right mailing list I talk to. Please let me > know if I had violated any unwritten rules. :) > > I have a global variable PGconn* m_pgconn that is the connection handle > for connecting to the postgresql database. Can I access/use the handle > from multiple threads? Say I have a thread that does some insertion > through this handle/database connection(m_pgconn) and also another > thread that do some insertion *on the same table* through this > handle(m_pgconn), will that break? > > Thanks > > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Problems using UPDATE and SUM
I am having a SUM problem also. Here is what I am trying to do. There are two tables: cust_rfq1_2 and inventory1 cust_rfq1_2 has four columns: rfq_id(int4),qty(int4),part_id (varchar 30),rev (varchar 2) inventory1 has three columns: part_id (varchar 30),rev (varchar 2), qty_instock (int4) Data in the cust_rfq1_2 looks like this: 4, 5,parta,01 4,10,parta,01 4,10,partb,01 Data in the inventory1 looks like this: parta,01,100 partb,01,100 When I use the select cust_rfq1_2.part_id,cust_rfq1_2.rev,SUM(cust_rfq1_2 from cust_rfq1_2 where cust_rfq1_2.part_id=inventory1.part_id and cust_rfq1_2.rev=inventory1.rev and cust_rfq1_2.rfq_id ='4' group by inventory1.part_id,inventory1.rev" I get good results: part_id,rev,sum parta,01,15 partb,01,10 However, when I use this sql statement for update: "update inventory1 set qty_instock=inventory1.qty_instock + cust_rfq1_2.qty where cust_rfq1_2.qty = ANY (select SUM(cust_rfq1_2.qty) from cust_rfq1_2 where cust_rfq1_2.part_id=inventory1.part_id and cust_rfq1_2.rev=inventory1.rev and cust_rfq1_2.rfq_id='4' )" I get this: inventory1 Results After the update part_id,rev,qty parta,01,100 partb,01,110 inventory1 Results before the update part_id,rev,qty parta,01,100 partb,01,100 inventory1 Results Should be After update part_id,rev,qty parta,01,115 partb,01,110 Any suggestions would be appreicated. Tyge Cawthon ---(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] Conversion
Hi all, How can I convert from int4 to date time? Regards, Lonh
Re: [SQL] Conversion
Is the int4 a UNIX epoch? ie. seconds since 1970? If so, then this will generally work: SELECT CAST(int4field AS abstime); or SELECT int4field::abstime; Chris -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Lonh SENGSent: Tuesday, 13 August 2002 11:14 AMTo: [EMAIL PROTECTED]Subject: [SQL] Conversion Hi all, How can I convert from int4 to date time? Regards, Lonh
[SQL] 答复: [SQL] Is this valid?
I am using C++ and libpq. Thanks Wei -ÓʼþÔ¼þ- ·¢¼þÈË: Robert Treat [mailto:[EMAIL PROTECTED]] ·¢ËÍʱ¼ä: Monday, August 12, 2002 8:10 PM ÊÕ¼þÈË: Wei Weng ³ËÍ: [EMAIL PROTECTED] Ö÷Ìâ: Re: [SQL] Is this valid? I'm going to ask the crazy question of what language/interface are you using to interact with postgres? Based on my interpretation of your question I'd say that won't break (though one of your queries might fail) but then again I may be totally misreading what you wrote... Robert Treat On Mon, 2002-08-12 at 19:21, Wei Weng wrote: > I am not sure if this is the right mailing list I talk to. Please let me > know if I had violated any unwritten rules. :) > > I have a global variable PGconn* m_pgconn that is the connection handle > for connecting to the postgresql database. Can I access/use the handle > from multiple threads? Say I have a thread that does some insertion > through this handle/database connection(m_pgconn) and also another > thread that do some insertion *on the same table* through this > handle(m_pgconn), will that break? > > Thanks > > > -- > Wei Weng > Network Software Engineer > KenCast Inc. > > > > ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is this valid?
Wei Weng wrote: >I am not sure if this is the right mailing list I talk to. Please let me >know if I had violated any unwritten rules. :) > >I have a global variable PGconn* m_pgconn that is the connection handle >for connecting to the postgresql database. Can I access/use the handle >from multiple threads? Say I have a thread that does some insertion >through this handle/database connection(m_pgconn) and also another >thread that do some insertion *on the same table* through this >handle(m_pgconn), will that break? > >Thanks > > > > I don't think it's a good idea to use global variable in multi-thread environment. I think use different handle in differents is better. libpq is thread safe, but it doesn't necessary mean that you could free of your own code. regards laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Function error
Hi , I'm porting MS- SQL stored procedure to postgres . I'm getting this error : Error occurred while executing PL/pgSQL function sel_free_disk_space line 7 at SQL statementSELECT query has no destination for result data.If you want to discard the results, use PERFORM instead. Here is the MS -SQL stored procedure : CREATE procedure sel_free_disk_space @computer_id int, @letter char(1)as declare @free int select @free = [free] from logical_drive where computer_id = @computer_id and letter = upper(@letter) if (@free is null) set @free = -1 return @freeGO Here is the equivalent function for Postgres : CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer AS 'DECLARE -- Declare variable to store the free space. free INTEGER; BEGIN select free from logical_drive where computer_id = $1 and letter = upper($2); IF free IS NULL THEN RETURN -1; END IF; RETURN free;END;'LANGUAGE 'plpgsql'; I'm not able to understand what I'm missing ? Secondly is there any equivalent of exec for postgres ? Any help will be highly appreciated. Regards, -Sugandha
Re: [SQL] Function error
Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah: > Hi , > > I'm porting MS- SQL stored procedure to postgres . I'm getting this > error : > > Error occurred while executing PL/pgSQL function > sel_free_disk_space line 7 at SQL statement > SELECT query has no destination for result data. > If you want to discard the results, use PERFORM instead. you need to SELECT INTO if you want to set a varaible in plpgsql try this: DECLARE var_free integer; -- var_free is used just to name it differently from the column name BEGIN SELECT INTO var_free free from logical_drive where computer_id = $1 and letter = upper($2); IF var_free THEN ... Janning > CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer > AS ' DECLARE > -- Declare variable to store the free space. > free INTEGER; > > BEGIN >select free from logical_drive where computer_id = $1 and > letter = upper($2); > IF free IS NULL THEN >RETURN -1; > END IF; > > RETURN free; > END; > 'LANGUAGE 'plpgsql'; > > > I'm not able to understand what I'm missing ? > > Secondly is there any equivalent of exec for postgres ? > > Any help will be highly appreciated. > > Regards, > -Sugandha -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Conversion
On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote: > Is the int4 a UNIX epoch? ie. seconds since 1970? > > If so, then this will generally work: > > SELECT CAST(int4field AS abstime); > > or > > SELECT int4field::abstime; http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: The types abstime and reltime are lower precision types which are used internally. You are discouraged from using any of these types in new applications and are encouraged to move any old ones over when appropriate. Any or all of these internal types might disappear in a future release. Don't they? -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Conversion
> On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote: > > Is the int4 a UNIX epoch? ie. seconds since 1970? > > > > If so, then this will generally work: > > > > SELECT CAST(int4field AS abstime); > > > > or > > > > SELECT int4field::abstime; > > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: > > The types abstime and reltime are lower precision types which are used > internally. You are discouraged from using any of these types in new > applications and are encouraged to move any old ones over when > appropriate. Any or all of these internal types might disappear in a > future release. Yes, but in absence of: SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234'); (Hint Hint Thomas!!!) It's all he can do. I suggest using the syntax above to convert his integer column to a timestamp column. Chris ---(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
