[SQL] Returning with the inserted id
Hi all
A table was created with:
CREATE TABLE test (
id integer,
nev varchar(25),
datum timestamp
);
A sequence to hold the id was defined with:
CREATE SEQUENCE "public"."test_azon_seq"
INCREMENT 1 MINVALUE 101
MAXVALUE 9223372036854775807 START 101
CACHE 1;
The function wich allocates the id and defines the datum is:
CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
BEGIN
select into NEW.id nextval('test_azon_seq');
NEW.datum := current_timestamp;
RETURN NEW;
END;
$test_verif$ LANGUAGE plpgsql;
and the before insert trigger is:
CREATE TRIGGER test_verif
BEFORE INSERT ON test
FOR EACH ROW
EXECUTE PROCEDURE test_verif();
When I issue an insert (see below) how can I retrieve the
inserted value of id? I need something like Oracle's returns
for insert.
insert into "public"."test" (nev) values ('text');
--
László Graf
---(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] Returning with the inserted id
Graf László wrote:
A sequence to hold the id was defined with:
CREATE SEQUENCE "public"."test_azon_seq"
INCREMENT 1 MINVALUE 101
MAXVALUE 9223372036854775807 START 101
CACHE 1;
The function wich allocates the id and defines the datum is:
CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
BEGIN
select into NEW.id nextval('test_azon_seq');
NEW.datum := current_timestamp;
RETURN NEW;
END;
$test_verif$ LANGUAGE plpgsql;
I take it this is just an example, because you could do this with
DEFAULTs on both columns.
When I issue an insert (see below) how can I retrieve the
inserted value of id? I need something like Oracle's returns
for insert.
insert into "public"."test" (nev) values ('text');
SELECT currval('public.test_azon_seq');
And yes, it will cope with multiple concurrent connections inserting.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] cursor "" already in use
Hi, list.
I've created a stored function in plpgsql which uses some functions from
postgis.
CREATE OR REPLACE FUNCTION "public"."bufferfeatures" (integer [], text,
text, double precision) RETURNS SETOF "public"."shapedummy" AS
$body$
DECLARE
source_layer_features ALIAS FOR $1;
source_layer ALIAS FOR $2;
target_layer ALIAS FOR $3;
buffer_radius ALIAS FOR $4;
source_rec shapedummy%ROWTYPE;
target_rec record;
return_rec shapedummy%ROWTYPE;
source_curs refcursor;
target_curs refcursor;
str text;
BEGIN
str := array_to_string(source_layer_features, ',');
str := 'ARRAY[' || str || ']';
open source_curs for EXECUTE 'SELECT * from getBuffer(' ||str|| ','
||quote_literal(source_layer)|| ',' ||quote_literal(buffer_radius)|| ')';
loop
fetch source_curs into source_rec;
EXIT WHEN NOT FOUND;
open target_curs for execute 'select gid, the_geom, intersects('
||quote_literal(source_rec.the_geom)|| ', the_geom) as iss from ' ||
target_layer;
loop
fetch target_curs into target_rec;
EXIT WHEN NOT FOUND;
if target_rec.iss = '1' then
return_rec.gid := target_rec.gid;
return_rec.the_geom := target_rec.the_geom;
RETURN NEXT return_rec;
end if;
end loop;
end loop;
CLOSE source_curs;
CLOSE target_curs;
RETURN ;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I've tested the queries extensively and they return some results if i
run them with my own parameters from psql.
When i run " select * from bufferFeatures(ARRAY[42,31],'countries',
'cities', 2000) " i get the following error :
ERROR: cursor "" already in use
CONTEXT: PL/pgSQL function "bufferfeatures" line 19 at open
Do you have any ideas ?
Thanks in advance.
Stathis Stergou
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] cursor "" already in use
On Fri, Sep 02, 2005 at 02:17:52PM +0300, Stathis Stergou wrote: > ERROR: cursor "" already in use > CONTEXT: PL/pgSQL function "bufferfeatures" line 19 at open You're trying to open a cursor that's already open. Close the cursor at the end of the loop in which you opened it, so that it gets closed before you try to open it again on the next loop iteration. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] not sure about constraints
Hello, I've got 4 tables : CREATE TABLE tableA (id SERIAL PRIMARY KEY NOT NULL, blahA TEXT); CREATE TABLE tableB (id SERIAL PRIMARY KEY NOT NULL, blahB TEXT); CREATE TABLE tableC (id SERIAL PRIMARY KEY NOT NULL, ida INT4 REFERENCES tableA(id), idb INT4 REFERENCES tableB(id), blahC TEXT); CREATE TABLE tableD (id SERIAL PRIMARY KEY NOT NULL, ida INT4, idb INT4, blahC TEXT, CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); Is the definition of tableD sufficient, or should I do it this way instead : CREATE TABLE tableD (id SERIAL PRIMARY KEY NOT NULL, ida INT4 REFERENCES tableA(id), idb INT4 REFERENCES tableB(id), blahD TEXT, CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); which looks superfluous to me. ??? Thanks in advance Jerome Alet ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] not sure about constraints
O Jerome Alet έγραψε στις Sep 2, 2005 : > Hello, > > I've got 4 tables : > > CREATE TABLE tableA (id SERIAL PRIMARY KEY NOT NULL, > blahA TEXT); > > CREATE TABLE tableB (id SERIAL PRIMARY KEY NOT NULL, > blahB TEXT); > > CREATE TABLE tableC (id SERIAL PRIMARY KEY NOT NULL, > ida INT4 REFERENCES tableA(id), > idb INT4 REFERENCES tableB(id), > blahC TEXT); > > CREATE TABLE tableD > (id SERIAL PRIMARY KEY NOT NULL, > ida INT4, > idb INT4, > blahC TEXT, > CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); The above statement is in error. In order to create FK to another table (tableC), you must do this on tableC's PK (id) or some UNIQUE key in general. > > Is the definition of tableD sufficient, or should I do it this way > instead : > > CREATE TABLE tableD > (id SERIAL PRIMARY KEY NOT NULL, > ida INT4 REFERENCES tableA(id), > idb INT4 REFERENCES tableB(id), > blahD TEXT, > CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); > Provided you have done something like ALTER TABLE tablec add CONSTRAINT tablec_ukey UNIQUE (ida,idb); Which means that each compination of the pair (ida,idb) in tablec is unique in tablec, i.e. NO 2 rows of tablec have the same (ida,idb), Then you can go which chioce 1), since it is guaranteed that ida belongs to tablea, and idb belongs to tableb by tablec's contraints. So the extra FKs defined in choice 2) are indeed reduntant. But the point here is to understand, that always when we point to another table, we point at some Unique key of that table. > which looks superfluous to me. > > ??? > > Thanks in advance > > Jerome Alet > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- -Achilleus ---(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] not sure about constraints
On Fri, Sep 02, 2005 at 04:43:15PM +0300, Achilleus Mantzios wrote: > O Jerome Alet ?? Sep 2, 2005 : > > > I've got 4 tables : > > > > CREATE TABLE tableA (id SERIAL PRIMARY KEY NOT NULL, > > blahA TEXT); > > > > CREATE TABLE tableB (id SERIAL PRIMARY KEY NOT NULL, > > blahB TEXT); > > > > CREATE TABLE tableC (id SERIAL PRIMARY KEY NOT NULL, > > ida INT4 REFERENCES tableA(id), > > idb INT4 REFERENCES tableB(id), > > blahC TEXT); > > > > CREATE TABLE tableD > > (id SERIAL PRIMARY KEY NOT NULL, > > ida INT4, > > idb INT4, > > blahC TEXT, > > CONSTRAINT checkAB FOREIGN KEY (ida, idb) REFERENCES tableC(ida, idb)); > > The above statement is in error. > In order to create FK to another table (tableC), you must do this > on tableC's PK (id) or some UNIQUE key in general. sorry, while adapting the code for the purpose of posting here, I've forgotten that I've this line in my real script : CREATE UNIQUE INDEX tableCindex ON tableC (ida, idb); So I think tableD should be OK (BTW I don't have any error) Thanks for your help bye Jerome Alet ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Turn off auto-commit
Im working in postgresql 8.0 in windows XP. How can I turn off auto-commit. I already tried a few things but it didn't worked.Regards João Carvalho Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!
Re: [SQL] Turn off auto-commit
You can turn off autocommit by explicitly starting a transaction (eg. with BEGIN; in psql or by turning off autocommit through ODBC). Statements inside the transaction will not have their own, automatically-commited transactions. HTH, Owen -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Joÿffe3o CarvalhoSent: Friday, September 02, 2005 10:22 AMTo: PG-SQL lista de mailSubject: [SQL] Turn off auto-commit Im working in postgresql 8.0 in windows XP. How can I turn off auto-commit. I already tried a few things but it didn't worked.Regards João Carvalho Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!
[SQL] Recommendation on bytea or blob for binary data like images
Hi, I'd like to know what the official recommendation is on which binary datatype to use for common small-binary size use. I'm working with the Open For Business (www.ofbiz.org) framework, which by default maps binary data, such as shipping label images, into OID field types. In general, the data is far less than a gigabyte in size. Is it strongly recommended by the postgresql community to store things like this in bytea format as of 8.0? Thanks, Leon Torres [EMAIL PROTECTED] Open Source Strategies http://opensourcestrategies.com ---(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] Recommendation on bytea or blob for binary data like images
[EMAIL PROTECTED] writes: > Hi, I'd like to know what the official recommendation is on which binary > datatype to use for common small-binary size use. If bytea will work for you, it's definitely the thing to use. The only real drawback to bytea is that there's currently no API to read and write bytea values in a streaming fashion. If your objects are small enough that you can load and store them as units, bytea is fine. BLOBs, on the other hand, have a number of drawbacks --- hard to dump, impossible to secure, etc. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] insert only if conditions are met?
Thanks for all your answers. Very helpful. What if after adding all those hours in one long transaction, I want to send a query to check the MONTHLY TOTAL HOURS (including those just entered) and if they exceed N number of hours, all those records added should *ROLLBACK*? BEGIN; insert.. insert. if sum(hours)>N then ROLLBACK END; Is that possible? Maybe with just plain SQL? (and one transaction) On 8/31/05, Philip Hallstrom <[EMAIL PROTECTED]> wrote: > On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:>> Ok. Here's TABLE A empdate hours type>> JSMITH 08-15-2005 5 WORK>> JSMITH 08-15-2005 3 WORK>> JSMITH 08-25-2005 6 WORK I want to insert the ff:>> 1.) JSMITH08-15-20058VAC>> 2.) DOE08-16-20058VAC #1 should fail because there is already 8 hours entered as being >> Worked on 08-15-2005 (same date).>> sorry, did not notice the duplicates before my previous reply.>> you could do something like> insert into A select 'JSMITH','08-15-2005',8,'VAC' >where> 8 != (select sum(hours) FROM A>WHERE emp = 'JSMITH'>AND date = '8-15-2005');Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'mguessing he'd still want it to fail since adding that 8 hours ov VAC wouldresult in a 15 hour day... so maybe something like? insert into A select 'JSMITH','08-15-2005',8,'VAC'WHERE8 >= 8 + (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005');?
Re: [SQL] insert only if conditions are met?
On Fri, 2 Sep 2005, Henry Ortega wrote: Thanks for all your answers. Very helpful. What if after adding all those hours in one long transaction, I want to send a query to check the MONTHLY TOTAL HOURS (including those just entered) and if they exceed N number of hours, all those records added should *ROLLBACK*? BEGIN; insert.. insert. if sum(hours)>N then ROLLBACK END; Is that possible? Maybe with just plain SQL? (and one transaction) Just add in another where clause using AND and modify the values to sum the hours for the entire month instead of just the day. At least I think that would do it. On 8/31/05, Philip Hallstrom <[EMAIL PROTECTED]> wrote: On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote: Ok. Here's TABLE A emp date hours type JSMITH 08-15-2005 5 WORK JSMITH 08-15-2005 3 WORK JSMITH 08-25-2005 6 WORK I want to insert the ff: 1.) JSMITH 08-15-2005 8 VAC 2.) DOE 08-16-2005 8 VAC #1 should fail because there is already 8 hours entered as being Worked on 08-15-2005 (same date). sorry, did not notice the duplicates before my previous reply. you could do something like insert into A select 'JSMITH','08-15-2005',8,'VAC' where 8 != (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005'); Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'm guessing he'd still want it to fail since adding that 8 hours ov VAC would result in a 15 hour day... so maybe something like? insert into A select 'JSMITH','08-15-2005',8,'VAC' WHERE 8 >= 8 + (select sum(hours) FROM A WHERE emp = 'JSMITH' AND date = '8-15-2005'); ? ---(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
[SQL] Searching for results with an unknown amount of data
Hi, I've got two tables, A and B, the first one containing columns A_ID | info where A_ID is primary, so that this table stores various information about an object, and the second containing columns A_ID | property where property is an integer referring to a particular property that an object may possess. I've seperated these into two tables so that an object may have several (ie an unknown number of) properties. Now, I want to find all objects that have at most properties 1,2,3, say (so something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this -- can anyone help? Also, what if I want to find all the objects possessing properties 4,5,6, as well as possibly other things? I've done it with nested SELECTs (constructed programmatically), but I think this is quite slow and not a very pleasing solution. Obviously, both these things will need to be done for an arbitrary list of integers. Thanks, DL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
