[SQL] get sequence value of insert command
hi
create sequence mysequence;
create table foo(
id integer default nextval('mysequence'),
bla text,
wombat integer,
foobar date,
primary key(id)
);
insert into foo (wombat) values (88);
now how do i know the id of my newly inserted element? and
how can this be done in a completely concurrency safe way?
cya
erik
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] get sequence value of insert command
O Erik Thiele έγραψε στις Nov 19, 2004 :
> hi
>
> create sequence mysequence;
>
> create table foo(
> id integer default nextval('mysequence'),
> bla text,
> wombat integer,
> foobar date,
> primary key(id)
> );
>
> insert into foo (wombat) values (88);
>
> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?
The way to do this is by reading the docs :)
use currval, it is session safe.
>
>
> cya
> erik
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
--
-Achilleus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] get sequence value of insert command
Achilleus Mantzios wrote: now how do i know the id of my newly inserted element? and how can this be done in a completely concurrency safe way? The way to do this is by reading the docs :) use currval, it is session safe. The increase in this question suggests the number of new users has increased since 8.0 went into beta-test. It's pretty much the first question anyone asks. I seem to recall it was mine. I made the mistake of assuming it wasn't concurrency safe and was gently corrected by one of the community. I think it might have been a Tim/Tam Lane. Wonder what happened to him? :-) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] get sequence value of insert command
On Nov 19, 2004, at 5:41 PM, Erik Thiele wrote: now how do i know the id of my newly inserted element? and how can this be done in a completely concurrency safe way? This is a FAQ (4.15.1, among others). See currval() and nextval() in the documentation as well. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] get sequence value of insert command
I seem to recall it was mine. I made the mistake of assuming it wasn't concurrency safe and was gently corrected by one of the community. I think it might have been a Tim/Tam Lane. Wonder what happened to him? :-) M tim tams rgds Homer ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] transactions in functions, possible bug or what I'm doing wrong?
Hi All,
PostgreSQL 7.4.5
assume this script:
---
create table test_table
(
id serial,
test_value text
) without oids;
insert into test_table
(test_value)
values ('A');
insert into test_table
(test_value)
values ('B');
insert into test_table
(test_value)
values ('C');
insert into test_table
(test_value)
values ('D');
CREATE OR REPLACE FUNCTION test_with_transaction()
RETURNS text AS
'declare my_test_record record;
declare my_return_value text;
begin
my_return_value := ;
start transaction;
for my_test_record in select * from test_table
loop
my_return_value := my_return_value || my_test_record.test_value;
end loop;
return my_return_value;
commit;
end;'
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION test_without_transaction()
RETURNS text AS
'declare my_test_record record;
declare my_return_value text;
begin
my_return_value := ;
for my_test_record in select * from test_table
loop
my_return_value := my_return_value || my_test_record.test_value;
end loop;
return my_return_value;
end;'
LANGUAGE 'plpgsql' VOLATILE;
---
Why does select test_without_transaction();
return this info:
"ABCD" (as should be)
and select test_with_transaction();
returns this error?
ERROR: SPI_prepare() failed on "start transaction"
CONTEXT: PL/pgSQL function "test_with_transaction" line 6 at SQL
statement
I've been investigating the matter in the doc I have, but to no avail.
google was not helpful either.
any suggestion? is this a bug? or the bug resides in my head?
regards,
Riccardo
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] transactions in functions, possible bug or what I'm doing
Riccardo G. Facchini wrote: Why does select test_without_transaction(); return this info: "ABCD" (as should be) and select test_with_transaction(); returns this error? ERROR: SPI_prepare() failed on "start transaction" CONTEXT: PL/pgSQL function "test_with_transaction" line 6 at SQL statement I've been investigating the matter in the doc I have, but to no avail. google was not helpful either. The function-call is already within a transaction (every command is), and you can't have nested transactions in 7.4. You can have what are called "savepoints" in version 8.0 though, which lets you trap errors and rollback to a named (saved) point in your function. -- Richard Huxton Archonet Ltd ---(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] CREATE TEMPORARY TABLE .. ON COMMIT DROP question
Hi all,
I'm trying to understand where the "[ ON COMMIT { PRESERVE ROWS |
DELETE ROWS | DROP } ]" is stored when defining a temporary table.
whenever a table is created, a record in the pg_class is stored with
the info regarding the table, but I haven't been able to locate where
the info regarding these particular parameters is stored.
any suggestion?
regards,
Riccardo
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] CREATE TEMPORARY TABLE .. ON COMMIT DROP question
"Riccardo G. Facchini" <[EMAIL PROTECTED]> writes:
> I'm trying to understand where the "[ ON COMMIT { PRESERVE ROWS |
> DELETE ROWS | DROP } ]" is stored when defining a temporary table.
I don't believe it's stored anyplace visible :-(. There's some private
state in the memory of the backend that owns the table. Look into
commands/tablecmds.c.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] CREATE TEMPORARY TABLE .. ON COMMIT DROP question
--- Tom Lane <__> wrote:
> "Riccardo G. Facchini" <[EMAIL PROTECTED]> writes:
> > I'm trying to understand where the "[ ON COMMIT { PRESERVE ROWS |
> > DELETE ROWS | DROP } ]" is stored when defining a temporary table.
>
> I don't believe it's stored anyplace visible :-(. There's some
> private
> state in the memory of the backend that owns the table. Look into
> commands/tablecmds.c.
>
> regards, tom lane
>
[..]
Thanks, I'm reading the code at this right moment.
I'm wandering if this is consistent... I mean, a temporary table finds
its way to the pg_class as any other table, so I'm able to retrieve a
lot of things regarding that particular table. Even if a temporary
table is something that's assumed to be built by my own session, I may
want to know if what I store there is eventually destroyed.
this would mean extending the pg_class definition and generating the
associated code... not a big job, but something that may not be done
lightly.
I'm writing a set of utilities that run on pure plpgsql that provide
info about the objects contained in the database, and I started with
one of those things that are not provided by the pg_xxx functions, the
table. My code is capable of rendering the code of a temporary table,
but not capturing the info regarding the "ON COMMIT" part...
regards,
R.
---(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
Re: [SQL] Comparing Dates
You should use single quotes for all literals. Examples: select '2004-06-08' ; ?column? 2004-06-08 select 'user' ; ?column? -- user Failing to quote literals will cause unexpected results. Examples: select 2004-06-08 ; ?column? -- 1990 select user ; current_user -- guy "Thomas F.O'Connell" <[EMAIL PROTECTED]> writes: select 2004-06-08; ?column? -- 1990 I'm not exactly sure how the bare string is converted internally, but it's clearly not a complete date like you're expecting. -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] get sequence value of insert command
> -Original Message-
> From: Erik Thiele [mailto:[EMAIL PROTECTED]
> Sent: Friday, November 19, 2004 3:42 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] get sequence value of insert command
>
>
> hi
>
> create sequence mysequence;
>
> create table foo(
> id integer default nextval('mysequence'),
> bla text,
> wombat integer,
> foobar date,
> primary key(id)
> );
>
> insert into foo (wombat) values (88);
>
> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?
CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
currval('mysequence') AS id LIMIT 1;
>
>
> cya
> erik
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] transactions in functions, possible bug or what I'm doing
On Fri, Nov 19, 2004 at 12:59:07PM +, Richard Huxton wrote:
> You can have what are called "savepoints" in version 8.0 though, which
> lets you trap errors and rollback to a named (saved) point in your function.
Savepoints in functions don't work as of 8.0.0beta4, unless I'm
doing something wrong:
CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE FUNCTION fooins(TEXT) RETURNS BOOLEAN AS $$
BEGIN
SAVEPOINT x;
INSERT INTO foo (name) VALUES ($1);
ROLLBACK TO x;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT fooins('John');
ERROR: SPI_execute_plan failed executing query "SAVEPOINT x":
SPI_ERROR_TRANSACTION
CONTEXT: PL/pgSQL function "fooins" line 2 at SQL statement
Error trapping does work, however:
CREATE FUNCTION fooins(TEXT) RETURNS BOOLEAN AS $$
BEGIN
INSERT INTO foo (name) VALUES ($1 || '-1');
BEGIN
INSERT INTO foo (id, name) VALUES (currval('foo_id_seq'), $1 || '-2');
EXCEPTION
WHEN unique_violation THEN
NULL;
END;
INSERT INTO foo (name) VALUES ($1 || '-3');
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
SELECT fooins('John');
fooins
t
(1 row)
SELECT * FROM foo;
id | name
+
1 | John-1
2 | John-3
(2 rows)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(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
Re: [SQL] transactions in functions, possible bug or what I'm doing
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Fri, Nov 19, 2004 at 12:59:07PM +, Richard Huxton wrote: >> You can have what are called "savepoints" in version 8.0 though, which >> lets you trap errors and rollback to a named (saved) point in your function. > Savepoints in functions don't work as of 8.0.0beta4, unless I'm > doing something wrong: You can't use explicit savepoint commands, but the exception syntax does essentially the same thing implicitly. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] NULLS and string concatenation
I have a function that uses an execute statement to insert data into a table, I do in my implementation of table partitioning. Anyway, I ran into trouble when NULL values were being passed in (fields are nullable) and my insert statement turned into a big NULL. Here's an equivalent statement that caused trouble: select 'some text, should be null:'|| NULL This returns NULL and no other text. Why is that? I wasn't expecting the "some text.." to disappear altogether. Is this a bug? I was able to work around the problem by using COALESCE (and casting variables since it wants the same data types passed to it). -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(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] NULLS and string concatenation
On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake <[EMAIL PROTECTED]> wrote: > I have a function that uses an execute statement to insert data into a > table, I do in my implementation of table partitioning. > > Anyway, I ran into trouble when NULL values were being passed in > (fields are nullable) and my insert statement turned into a big NULL. > > Here's an equivalent statement that caused trouble: > > select 'some text, should be null:'|| NULL > > This returns NULL and no other text. Why is that? I wasn't expecting > the "some text.." to disappear altogether. > > Is this a bug? No. > > I was able to work around the problem by using COALESCE (and casting > variables since it wants the same data types passed to it). This is what you should do. > > -Don > > -- > Donald Drake > President > Drake Consulting > http://www.drakeconsult.com/ > 312-560-1574 > > ---(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 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] NULLS and string concatenation
On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote: > On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake <[EMAIL PROTECTED]> wrote: > > > > I was able to work around the problem by using COALESCE (and casting > > variables since it wants the same data types passed to it). > > This is what you should do. If you don't mind using a non-standard feature, another possibility would be to create an operator similar to || that COALESCEs NULLs into empty strings. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] NULLS and string concatenation
Don Drake wrote: select 'some text, should be null:'|| NULL This returns NULL and no other text. Why is that? I wasn't expecting the "some text.." to disappear altogether. Is this a bug? No. Null is "unknown" if you append unknown (null) to a piece of text, the result is unknown (null) too. If you're using NULL to mean something other than unknown, you probably want to re-examine your reasons why. I was able to work around the problem by using COALESCE (and casting variables since it wants the same data types passed to it). That's the correct procedure (although ask yourself if you should have nulls rather than just empty strings). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] NULLS and string concatenation
On Fri, 19 Nov 2004 17:48:34 +, Richard Huxton <[EMAIL PROTECTED]> wrote: > Don Drake wrote: > > select 'some text, should be null:'|| NULL > > > > This returns NULL and no other text. Why is that? I wasn't expecting > > the "some text.." to disappear altogether. > > > > Is this a bug? > > No. Null is "unknown" if you append unknown (null) to a piece of text, > the result is unknown (null) too. > > If you're using NULL to mean something other than unknown, you probably > want to re-examine your reasons why. > I'm using NULL to mean no value. Logically, NULL is unknown, I agree. I'm trying to dynamically create an INSERT statement in a function that sometimes receives NULL values. This is still strange to me. In Oracle, the same query would not replace the *entire* string with a NULL, it treats the NULL as a no value. I can't find in the documentation where string concatenation of any string and NULL is NULL. -Don ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] NULLS and string concatenation
On Fri, 19 Nov 2004, Don Drake wrote: > On Fri, 19 Nov 2004 17:48:34 +, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Don Drake wrote: > > > select 'some text, should be null:'|| NULL > > > > > > This returns NULL and no other text. Why is that? I wasn't expecting > > > the "some text.." to disappear altogether. > > > > > > Is this a bug? > > > > No. Null is "unknown" if you append unknown (null) to a piece of text, > > the result is unknown (null) too. > > > > If you're using NULL to mean something other than unknown, you probably > > want to re-examine your reasons why. > > > > I'm using NULL to mean no value. Logically, NULL is unknown, I agree. > > I'm trying to dynamically create an INSERT statement in a function > that sometimes receives NULL values. > > This is still strange to me. In Oracle, the same query would not > replace the *entire* string with a NULL, it treats the NULL as a no > value. Oracle has some incompatibilities with the SQL spec (at least 92/99) wrt NULLs and empty strings so it isn't a good comparison point. The spec is pretty clear that if either argument to concatenation is NULL the output is NULL. > I can't find in the documentation where string concatenation of any > string and NULL is NULL. I'm not sure it does actually. I'd have expected to see some general text on how most operators return NULL for NULL input but a quick scan didn't find any. ---(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
Re: [SQL] NULLS and string concatenation
Don Drake <[EMAIL PROTECTED]> writes: > This is still strange to me. In Oracle, the same query would not > replace the *entire* string with a NULL, it treats the NULL as a no > value. Oracle is a bit, um, standards-challenged. They fail to make a distinction between an empty string and a NULL, but such a distinction is both logically necessary and required by the SQL standard. > I can't find in the documentation where string concatenation of any > string and NULL is NULL. SQL92 section 6.13 , General Rule 2a: a) If either S1 or S2 is the null value, then the result of the is the null value. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NULLS and string concatenation
Someone on this list provided me with a rather elegant solution to this a few weeks ago: CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql; CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text); And I call it as: SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda) Deals quite neatly with the NULLs in some of the columns. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Fri 11/19/2004 9:53 AM To: Don Drake; [EMAIL PROTECTED] Cc: Subject:Re: [SQL] NULLS and string concatenation On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote: > On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake <[EMAIL PROTECTED]> wrote: > > > > I was able to work around the problem by using COALESCE (and casting > > variables since it wants the same data types passed to it). > > This is what you should do. If you don't mind using a non-standard feature, another possibility would be to create an operator similar to || that COALESCEs NULLs into empty strings. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NULLS and string concatenation
Slick ;o) This goes in my tool kit... On Friday 19 November 2004 03:03 pm, Gregory S. Williamson saith: > Someone on this list provided me with a rather elegant solution to this a > few weeks ago: > > CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, > text) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL > THEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql; > > CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, > LEFTARG = text, RIGHTARG = text); > > And I call it as: > SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ > trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda) > > Deals quite neatly with the NULLs in some of the columns. > > HTH, > > Greg Williamson > DBA > GlobeXplorer LLC > > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Fri 11/19/2004 9:53 AM > To: Don Drake; [EMAIL PROTECTED] > Cc: > Subject: Re: [SQL] NULLS and string concatenation > > On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote: > > On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake <[EMAIL PROTECTED]> wrote: > > > I was able to work around the problem by using COALESCE (and casting > > > variables since it wants the same data types passed to it). > > > > This is what you should do. > > If you don't mind using a non-standard feature, another possibility > would be to create an operator similar to || that COALESCEs NULLs > into empty strings. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- Quote: 87 "The federal government has taken too much tax money from the people, too much authority from the states, and too much liberty with the Constitution." --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] NULLS and string concatenation
On Fri, 19 Nov 2004 15:01:42 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Don Drake <[EMAIL PROTECTED]> writes: > > This is still strange to me. In Oracle, the same query would not > > replace the *entire* string with a NULL, it treats the NULL as a no > > value. > > Oracle is a bit, um, standards-challenged. They fail to make a > distinction between an empty string and a NULL, but such a distinction > is both logically necessary and required by the SQL standard. > > > I can't find in the documentation where string concatenation of any > > string and NULL is NULL. > > SQL92 section 6.13 , General Rule 2a: > > a) If either S1 or S2 is the null value, then the result of the >is the null value. > > regards, tom lane > Thanks for the responses. I now have a better appreciation for the SQL standard and PostgreSQL. -Don ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
