Re: [SQL] [GENERAL] need ``row number``
> You are going to need a set returning function. It will have > to look up the expected boosters, the expected time elapsed, > and return them with their sequence numbers if they exists. > There is no easy way to do it in a view that I can think of. I am not convinced I'll need a SRF. I am not trying to calculate something that isn't there yet. I am just trying to join two views appropriately. I might have to employ some variant of Celko's integer helper table but I'm not sure how to proceed. Karsten > <<< Karsten Hilbert <[EMAIL PROTECTED]> 9/23 1:56p >>> > Hello all, > > yes, I know, "row number" isn't a concept that fits into the > relational model and I will only be asking for something > similar. > > explanation (actual views below) > > > I have a view that holds the vaccinations scheduled for a > patient (v_vaccs_scheduled4pat) depending on what vaccination > regimes that patient is on. There are typically between 1 to 5 > vaccinations per disease (indication/regime) which is expressed > in the vaccination sequence number. Some regimes also have > booster shots scheduled. Those boosters are to be given > regularly after a set interval. Those have the sequence number > field set to NULL. > > There is a second view that lists all the vaccinations > actually given to a patient per regime (v_pat_vacc4ind). > This view has dates when the shot was given but no sequence > number. > > I now want to create a view which correlates the two showing > me which actual vaccination corresponds to which scheduled > vaccination. This is what I cannot get my head wrapped around > although it is probably fairly straightforward. > > The conceptual solution would be to order actual vaccinations > by date per regime and number them (remember the "row number" > in the subject line ?). One would then join on that with the > sequence numbers from the scheduled vaccinations view and treat > any actual vaccinations where "row number" > max(sequence > number) as being boosters (medically this is correct, btw). > Yes, there can and will be several boosters for some regimes. > > raw data > > full schema here: > http://hherb.com/gnumed/schema/ > > full schema defs in CVS here: > > http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/gmClinicalViews.sql > > relevant excerpt: > > --- == > --- vaccination stuff > --- - > \unset ON_ERROR_STOP > drop view v_vacc_regimes; > \set ON_ERROR_STOP 1 > > create view v_vacc_regimes as > select > vreg.id as pk_regime, > vind.description as indication, > _(vind.description) as l10n_indication, > vreg.name as regime, > coalesce(vreg.comment, '') as comment, > vreg.fk_indication as pk_indication, > vreg.fk_recommended_by as pk_recommended_by > from > vacc_regime vreg, > vacc_indication vind > where > vreg.fk_indication = vind.id > ; > > comment on view v_vacc_regimes is > 'all vaccination schedules known to the system'; > > --- - > \unset ON_ERROR_STOP > drop view v_vacc_defs4reg; > \set ON_ERROR_STOP 1 > > create view v_vacc_defs4reg as > select > vreg.id as pk_regime, > vind.description as indication, > _(vind.description) as l10n_indication, > vreg.name as regime, > coalesce(vreg.comment, '') as reg_comment, > vdef.is_booster as is_booster, > vdef.seq_no as vacc_seq_no, > vdef.min_age_due as age_due_min, > vdef.max_age_due as age_due_max, > vdef.min_interval as min_interval, > coalesce(vdef.comment, '') as vacc_comment, > vind.id as pk_indication, > vreg.fk_recommended_by as pk_recommended_by > from > vacc_regime vreg, > vacc_indication vind, > vacc_def vdef > where > vreg.id = vdef.fk_regime > and > vreg.fk_indication = vind.id > order by > indication, > vacc_seq_no > ; > > comment on view v_vacc_defs4reg is > 'vaccination event definitions for all schedules known to the system'; > > --- - > \unset ON_ERROR_STOP > drop view v_vacc_regs4pat; > \set ON_ERROR_STOP 1 > > create view v_vacc_regs4pat as > select > lp2vr.fk_patient as pk_patient, > vvr.indication as indication, > vvr.l10n_indication as l10n_indication, > vvr.regime as regime, > vvr.comment as comment, > vvr.pk_regime as pk_regime, > vvr.pk_indication as pk_indication, > vvr.pk_recommended_by as pk_recommended_by > from > lnk_pat2vacc_reg lp2vr, > v_vacc_regimes vvr > where > vvr.pk_regime = lp2vr.fk_regime > ; > > comment on view v_vacc_regs4pat is > 'selection of configured vaccination schedules a patient is actually on'; > > --- ---
[SQL] INDEX problem
Hi all! I have such table: CREATE TABLE object ( id SERIAL, object_type_id int8 ); This table has 4 000 000 rows. There are 2 index: CREATE INDEX object_id_idx ON object(id); CREATE INDEX object_object_type_id_idx ON object(object_type_id); So: # EXPLAIN SELECT * FROM object WHERE id = 1::int8; Index Scan using object_id_idx on object (cost=0.00..92323.66 rows=23650 width=29) Index Cond: (id = 1::bigint) Here everything is O.K. # EXPLAIN SELECT * FROM object WHERE object_type_id = 1::int8; Index Scan using object_object_type_id_idx on object (cost=0.00..92323.66 rows=23650 width=29) Index Cond: (object_type_id = 1::bigint) Here everything is O.K. too... but! # EXPLAIN SELECT * FROM object WHERE object_type_id IN (1::int8, 21::int8); Seq Scan on object (cost=0.00..105730.00 rows=47182 width=29) Filter: ((object_type_id = 1::bigint) OR (object_type_id = 21::bigint)) The same results after: # EXPLAIN SELECT * FROM object WHERE object_type_id = 1::int8 OR object_type_id = 21::int8; Why Postgres didn't use index here ??? # EXPLAIN ANALYZE SELECT * FROM object WHERE object_type_id IN (1::int8, 21::int8); Seq Scan on object (cost=0.00..105730.00 rows=47182 width=29) (actual time=20744.910..20744.910 rows=0 loops=1) Filter: ((object_type_id = 1::bigint) OR (object_type_id = 21::bigint)) Total runtime: 20745.022 ms Best regards, Vladimir S. Tikhonjuk ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] select column by position
Is there a way to select a column in a table by its position
rather than the field name? If I understand correctly column position in a
table is fixed. I’ve not been able to find anything in the archives or
docs to indicate that it’s possible to select by position.
For example:
CREATE TABLE Accessions (
accession_id INT8 PRIMARY KEY DEFAULT nextval('accession_id_seq'),
accenumb VARCHAR(255) NOT NULL,
instcode_id INT4 NOT NULL CONSTRAINT Accessions_instcode_id_fk
REFERENCES Institutions(institution_id),
accename VARCHAR(255)
);
I would like to select accession_id by indicating column 1
somehow. Is this at all possible?
Cheers,
Jennifer
*
DISCLAIMER:
This email is from the Scottish Crop Research Institute, but the views expressed by the sender are not necessarily the views of SCRI and its subsidiaries. This email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed. It may not be disclosed or used by any other than that addressee.If you are not the intended recipient you are requested to preserve this confidentiality and you must not use, disclose, copy, print or rely on this e-mail in any way. Please notify [EMAIL PROTECTED] quoting the name of the sender and delete the email from your system.
Although SCRI has taken reasonable precautions to ensure no viruses are present in this email, neither the Institute nor the sender accepts any responsibility for any viruses, and it is your responsibility to scan the email and the attachments (if any).
Re: [SQL] [GENERAL] need ``row number``
Karsten Hilbert <[EMAIL PROTECTED]> writes:
> I am not convinced I'll need a SRF. I am not trying to
> calculate something that isn't there yet. I am just trying to
> join two views appropriately. I might have to employ some
> variant of Celko's integer helper table but I'm not sure how
> to proceed.
A fairly common hack for this is to use a sequence:
create temp sequence tseq;
select nextval('tseq'), * from (select order by ...) ss;
Note you must do any desired ORDER BY inside the subselect. If it's
outside then it happens after computation of the row numbers, which
is exactly not what you want.
The major limitation of this is that you need to do some auxiliary
operations to create or reset the semaphore before each query.
There are some related hacks in the archives that use PL functions
with private persistent state, instead of a sequence object.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[SQL] How to insert values in bytea?
Hi everyone, I have a table with a bytea-column: CREATE TABLE picture( id int primary key, preview bytea NOT NULL); How can I insert a value in the bytea-column 'preview'? The function 'lo_import()' does only work for oid. But what is about bytea? If somebody has any suggestions or ideas, that would be great. Thanks a lot in advance, Oliver ___ Gesendet von Yahoo! Mail - Jetzt mit 100MB Speicher kostenlos - Hier anmelden: http://mail.yahoo.de ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] need ``row number``
Tom Lane <[EMAIL PROTECTED]> writes:
> Karsten Hilbert <[EMAIL PROTECTED]> writes:
> > I am not convinced I'll need a SRF. I am not trying to
> > calculate something that isn't there yet. I am just trying to
> > join two views appropriately. I might have to employ some
> > variant of Celko's integer helper table but I'm not sure how
> > to proceed.
>
> A fairly common hack for this is to use a sequence:
>
> create temp sequence tseq;
> select nextval('tseq'), * from (select order by ...) ss;
But I thought she wanted to get the row number within a group. Not the row
number for the entire result set. A sequence can't do that. Or at least, I
suppose it could but it would be mighty strange to see setval() in a SELECT
query. And I can't think of how to detect the level break in a select query
either.
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] select column by position
On Fri, 24 Sep 2004, Jennifer Lee wrote: > Is there a way to select a column in a table by its position rather than > the field name? If I understand correctly column position in a table is > fixed. I've not been able to find anything in the archives or docs to > indicate that it's possible to select by position. The general response will be that you don't really want to do this. You should figure out some other way to query the database that doesn't rely on column position. For example, dropping and adding columns with ALTER TABLE command could change their positions. If you still want to go about it, I suppose it might be possible to write a PL/pgSQL stored procedure that determines the column name from the system catalogs, then uses EXECUTE to run the query with the real column name substituted in the query. See Executing Dynamic Commands in the documentation: http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Documentation about what is stored in the system catalogs can be found: http://www.postgresql.org/docs/7.4/interactive/catalogs.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
