Re: [SQL] fetching the id of a new row
Albert REINER wrote: > > I do not know of a way to insert and select in one statement without > the use of a function (what's the problem with those, by the way?), > but as far as I can tell nextval() will return the next value for any > backend, so if you have more than one backend inserting at the same > time you might end up inserting with the same id twice. Instead you Actually nextval() works fine across backends. It always increments the sequence, so repeated calls waste numbers. > should insert once, without specifying the id (so that the default > value, which must be set to nextval()) will be used; to obtain the id, > if indeed you need it, you can than select currval(), which is > guaranteed to work on a per-backend basis. Yep - it's either get nextval and insert or insert and check currval. - Richard Huxton
[SQL] Simulating LIMIT/OFFSET in a subquery
I need to write querys that return the second and third record from a 'visits' table, for each patient. (What I need to do is look at how many patients were diagnosed on the first/second/third visit to see a physician at the outpatient clinic). I can get at the first visit using DISTINCT ON: SELECT DISTINCT ON (sy.episodeid) sy.episodeid, fu.opdid FROM breast_tblfollowup fu, breast_sympt sy, outpatients opd WHERE sy.episodeid = opd.episodeid AND fu.opdid = opd.recordno ORDER BY sy.episodeid, dateopappt; ( breast_sympt is a view returning the subset of patients that i wish to examine, opdid is the primary key in breast_tblfollowup, which is linked one-to-one to the primary key of outpatients, called recordno ) ... but I run into difficulties getting the second and third. I tried: SELECT sy.episodeid, fu.opdid FROM breast_tblfollowup fu, breast_sympt sy WHERE fu.opdid = (SELECT fu2.opdid FROM breast_tblfollowup fu2, outpatients opd2 WHERE fu2.opdid = opd2.recordno AND opd2.episodeid = sy.episodeid ORDER BY opd2.dateopappt LIMIT 1 OFFSET 1); But of course, ORDER BY and LIMIT are not allowed in sub-queries. Does anyone know how to work around this? I thought about creating a function that returns the primary key from the nth visit for a particular patient, but I am looking for a more general solution as I have a huge list of queries that all ask for similar things on different tables, and i don't want to have to create similar functions for each query. Thanks for your help! -- Jamie Walker [EMAIL PROTECTED]
[SQL] Contributing Documentation to PG
I finally came around to writing some extra documentation for PL/PgSQL (and maybe PL/Tcl) and how to do PL/SQL-to-PL/PgSQL porting. I downloaded the documentation sources, intending to use its nice configure/make scheme but I can't find the configure script as described in the current docs. Where can I find it? I know I can just use the Makefiles with a little tweaking, but I am wondering if I am missing something here. Thanks, -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Simulating LIMIT/OFFSET in a subquery
"Jamie Walker" <[EMAIL PROTECTED]> writes: > But of course, ORDER BY and LIMIT are not allowed in sub-queries. FWIW, they are allowed as of 7.1 ... regards, tom lane
