Re: [SQL] Having the sum of two queries
Thank you all! My problem has been solved in another way... because the inner queries had left outer joins and so on, and I finally have had to do two queries and treat results by PHP. Again... thank you all! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] data dependent sequences?
"Ragnar" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On sun, 2007-07-15 at 09:28 -0600, Stuart McGraw wrote: > > Advice requested :-) I have a table like: > > > > CREATE TABLE items ( > > id INT, > > typ INT... > > PRIMAY KEY (seq,typ)); > > > > I would like 'id' to be like a SERIAL except that I > > want independent sequences for each value of 'typ'. > > what possible advantage could there be to that? > > if you need gapless series, then sequences (and serial) > are not adequate anyways. > > just use one sequence. I am not looking for gapless sequences. The reason I want to do this is the "typ" column is actually an indicator of the source of the rest of the infomation in the row. The "rules" for assigning the id number vary depending on the source -- in some cases they start at 1 and increment by one, in other cases they start at, say, 100 and increment by 10. There are a lot existing data using these rules and I cannot change that. I can of course have the application do the assignments, but in general eould prefer to push this down into the database if posible. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] data dependent sequences?
"chester c young" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > > > CREATE TABLE items ( > > id INT, > > typ INT... > > PRIMAY KEY (seq,typ)); > > > > >id typ > > +- > > 1 'a' > > 2 'a' > > 3 'a' > > 1 'b' > > 4 'a' > > 2 'b' > > you will need to use pre insert trigger since you cannot use column > references in default expression. > > you could use this same trigger to either: > - create sequences as needed and apply the right one > - with locking, lookup for last id of typ > - with locking, keep another table of typ and nextval Thanks, that summerizes the options nicely. I noticed that sequences are tables with a single row that defines the sequence properties. I was hoping that there was some way of using a sequence with multiple rows to maintain multiplre sequences in a sngle table, which would make having a large number of sequences a little less cluttered, but sounds like I need to implement that from scratch. ---(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] data dependent sequences?
--- Stuart <[EMAIL PROTECTED]> wrote: > > you will need to use pre insert trigger since you cannot use column > > references in default expression. > > > > you could use this same trigger to either: > > - create sequences as needed and apply the right one > > - with locking, lookup for last id of typ > > - with locking, keep another table of typ and nextval here is supplementary information of gap-less sequences: http://www.varlena.com/GeneralBits/130.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] data dependent sequences?
On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote: > I am not looking for gapless sequences. The reason I > want to do this is the "typ" column is actually an indicator > of the source of the rest of the infomation in the row. Why do you need the sequence to be 1. . .n for each typ, then? If they're just there to preserve order, one sequence will work just fine. Otherwise, I think you have a normalisation problem. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(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] data dependent sequences?
"Andrew Sullivan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote: > > I am not looking for gapless sequences. The reason I > > want to do this is the "typ" column is actually an indicator > > of the source of the rest of the infomation in the row. > > Why do you need the sequence to be 1. . .n for each typ, then? If > they're just there to preserve order, one sequence will work just > fine. Otherwise, I think you have a normalisation problem. I probably shouldn't have indicated that 'typ' was part of the PK, or named the other column 'id'. There is actually a separate (surrogate) PK, and there is a unique index on the on (id,typ) only to prevent accidental dupicates. So 'id' is not really structually important -- it is a value that exists soley for the UI. In the app, the user can explicity request an explicit 'id' value. My desire to use a sequence to assign them is to handle the 99% common case where the user doesn't care about assigning a specific id, and just wants the "next" resonable value, consistent with the other values for that typ row. If there are 3 rows of typ=20 and 200 rows of typ=21, I don't want the next typ=20 row to get an id of 204, when the other rows have values of 1,2,3. This is simply a user expectation, based on existing data, that I can't change. I would just prefer to implement it in the database if possible rather than than the app. Hope I have clarified a little :-) ---(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] data dependent sequences?
On Tue, Jul 17, 2007 at 09:40:21AM -0600, Stuart wrote: > is not really structually important -- it is a value that > exists soley for the UI. Hmm. Maybe you should use generate_series() for the UI instead? It would always give you the order you like, you could use the universal sequence or whatever for your ORDER BY clause, and not store data that you actually don't care about. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(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] data dependent sequences?
"Andrew Sullivan" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Tue, Jul 17, 2007 at 09:40:21AM -0600, Stuart wrote: > > is not really structually important -- it is a value that > > exists soley for the UI. > > Hmm. Maybe you should use generate_series() for the UI instead? It > would always give you the order you like, you could use the universal > sequence or whatever for your ORDER BY clause, and not store data > that you actually don't care about. Except that the value does matter outside of the database and thus needs to be remembered. It is not used for ordering at all. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] data dependent sequences?
On Tue, Jul 17, 2007 at 12:23:21PM -0600, Stuart wrote: > > Except that the value does matter outside of the database and thus > needs to be remembered. It is not used for ordering at all. If you have two rows of data, where one column is data that varies as a function of the data in some other column, then what you have is data from two different tables, and not one table at all. At least, according to the rules of normalisation I know. My best guess, however, is that you're trying to implement a poor requirement. My bet is that if you dig harder, you'll find out what the requirement _really_ is, and then you won't have to implement what sounds like a bad idea. A -- Andrew Sullivan | [EMAIL PROTECTED] However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
Hi: I can't seem to resolve this issue. I have a fair sized SELECT statement that runs properly in a stored procedure using pl/pgSQL, but I have certain parts of the query that can filter by a NULL value, or a character string. What I can't seem to do is insert a conditional IF statement in my code. Here is how it starts: DECLARE r zrec_dd_holder%rowtype; BEGIN FOR r IN SELECT DISTINCT(pi.serial_number) AS "Incident ID", to_char(pi.incident_date,'Mon-dd-') AS "Incident date", to_char(pi.date_created,'Mon-dd-') AS "Report Date", CASE WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 1 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 2 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 3 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 4 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 5 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 6 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 7 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 8 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 9 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 10 THEN 4 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 11 THEN 4 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 12 THEN 4 END AS "Quarter", pf.name AS "Facility", pl.name AS "General Location", f.long_desc AS "Specific Location", .. The user can choose to allow the default (in this case NULL) or supply a predetermined value for "Specific Location". In the WHERE portion of the SELECT statment, I'd like to insert the IF statement shown below. I've tried single and double quotes and the pipeline for concatenation, but nothing seems to work. Any suggestions or resolutions would be greatly appreciated. Please feel free to contact me directly. AND pi.id = pid.id AND ( pid.incident_type_cid BETWEEN 117 AND 123 ) /--- IF $7 IS NOT NULL THEN AND f.id = pid.specific_location_cid AND f.long_desc = $7 END IF ---/ AND ( pi.location_id = pl.id ) AND pf.id = pl.facility_id AND pi.person_status_code_id = b.id regards, Norm ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
On 7/17/07, Norm Garand <[EMAIL PROTECTED]> wrote: /--- IF $7 IS NOT NULL THEN AND f.id = pid.specific_location_cid AND f.long_desc = $7 END IF ---/ How about: AND f.ID = pid.specific_location_cid AND f.long_desc = COALESCE ($7, f.long_desc) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code
On 7/17/07, Norm Garand <[EMAIL PROTECTED]> wrote: In the WHERE portion of the SELECT statment, I'd like to insert the IF statement shown below. I've tried single and double quotes and the pipeline for concatenation, but nothing seems to work. Any suggestions or resolutions would be greatly appreciated. Please feel free to contact me directly. use CASE baan=# create function t(con_stock boolean) returns setof ttdinv001214 as $$ baan$# select * from ttdinv001214 baan$# where substring(trim(t_item), 1, 3) = 'DPP' baan$# and case $1 when true then t_stoc > 0 else t_stoc = 0 end; baan$# $$ language sql; CREATE FUNCTION -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(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
[SQL] A better look at trying to insert a Conditional in a SELECT statement
Hi Again: thanks to those who sent me responses on my first post. I'll try to explain a bit better. There are several parameters that are passed by the user. Each of the parameters that are being passed, may have a NULL value, or an actual numerical value. The parameters filter a large report down a to small amount of records. With that being said, the problem I'm encountering is that I can't seem to insert a conditional (whether it be a CASE statement or an IF statement) in the middle of the SELECT statement. I'm not sure if the problem stems from using the ROWTYPE variable notation. Here is the complete code. It works as long as a NULL value doesn't get passed. To show what I mean, I'd like to insert the conditional around this line (23rd from the bottom): f.id = pid.specific_location_cid AND f.long_desc = $7 -- Function: sp_hirs_a_01dd(integer, character varying, integer, character varying, character varying, character varying, character varying) -- DROP FUNCTION sp_hirs_a_01dd(integer, character varying, integer, character varying, character varying, character varying, character varying); CREATE OR REPLACE FUNCTION sp_hirs_a_01dd(integer, character varying, integer, character varying, character varying, character varying, character varying) RETURNS SETOF rec_dd_holder AS $BODY$ DECLARE r rec_dd_holder%rowtype; BEGIN FOR r IN SELECT DISTINCT(pi.serial_number) AS "Incident ID", to_char(pi.incident_date,'Mon-dd-') AS "Incident date", to_char(pi.date_created,'Mon-dd-') AS "Report Date", CASE WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 1 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 2 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 3 THEN 1 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 4 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 5 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 6 THEN 2 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 7 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 8 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 9 THEN 3 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 10 THEN 4 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 11 THEN 4 WHEN EXTRACT ( MONTH FROM pi.incident_date ) = 12 THEN 4 END AS "Quarter", pf.name AS "Facility", pl.name AS "General Location", f.long_desc AS "Specific Location", b.long_desc AS "Status", pi.person_age AS "Age", CASE WHEN pi.gender_code_id ='31' THEN 'M' WHEN pi.gender_code_id ='32' THEN 'F' ELSE NULL END AS "Gender", to_char(pi.incident_date,'hh24:mm') AS "Time", pc.long_desc AS "Incident Type", a.long_desc AS "Incident Subtype", CASEWHEN pid.fallscf_behavoiur_val ='t' THEN 'Behaviour. ' WHEN pid.fallscf_behavoiur_val ='f' THEN '' END || CASEWHEN pid.fallscf_bowel_bladder_val ='t' THEN 'Bowel/Bladder Problem.' WHEN pid.fallscf_bowel_bladder_val ='f' THEN '' END || CASEWHEN pid.fallscf_comm_information_val ='t' THEN 'Communication/Information. ' WHEN pid.fallscf_comm_information_val = 'f' THEN '' END || CASEWHEN pid.fallscf_env_condition_val ='t' THEN 'Environmental Conditions. ' WHEN pid.fallscf_env_condition_val = 'f' THEN '' END || CASEWHEN pid.fallscf_eq_malfunction_val ='t' THEN 'Equipment Malfunction. ' WHEN pid.fallscf_eq_malfunction_val = 'f' THEN '' END || CASEWHEN pid.fallscf_eq_supplies_val ='t' THEN 'Equipment/Supplies Unavailable. ' WHEN pid.fallscf_eq_supplies_val = 'f' THEN '' END || CASEWHEN pid.fallscf_visitor_val ='t' THEN 'Family/Visitor Assisting. ' WHEN pid.fallscf_visitor_val = 'f' THEN '' END || CASEWHEN pid.fallscf_footwear_val ='t' THEN 'Inappropriate Footwear. 'WHEN pid.fallscf_footwear_val = 'f' THEN '' END || CASEWHEN pid.fallscf_instructionsnotfollowed_val ='t' THEN 'Instructions Not Followed. ' WHEN pid.fallscf_instructionsnotfollowed_val = 'f' THEN '' END || CASEWHEN pid.fallscf_interference_val ='t' THEN 'Interference/Interruption. ' WHEN pid.fallscf_interference_val = 'f' THEN '' END || CASEWHEN pid.fallscf_material_val ='t' THEN 'Material/Liquid on Floor. ' WHEN pid.fallscf_material_val = 'f' THEN '' END || CASEWHEN pid.fallscf_medication_val ='t' THEN 'Medication. ' WHEN pid.fallscf_medication_val = 'f' THEN '' END || CASEWHEN pid.fallscf_overreaching_val ='t' THEN 'Overreaching. ' WHEN pid.fallscf_overreaching_val = 'f' THEN '' END || CASEWHEN pid.fallscf_physical_cond_val ='t' THEN 'Physical/Medical Condition. ' WHE
