Hi,

First of all excuse me if i'm not precise enough, english is not my native
language.

I have a small problem while using transactions. I'm doing an INSERT in one
table, an UPDATE in another table and then i'm trying to retrieve the id
generated by the serial in the first table but the few things I have tryed
does not return anything. Here is a sample of my transaction:

BEGIN;
INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id =
'some_row_id';
SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I have tryed with RETURNING id on the INSERT too and pgadmin tells me each
time this kind of message : "query result of 1 line cancelled".

It works well when I do it without the begin and commit, so as a pgsql
beginner I'm a bit lost.


Guillaume Henriot

Hi,
which postgres version You are using?
do You perform this in function or as anonymous block, or at hoc query?

regards,
Bartek


2012/4/18 Guillaume Henriot <henriotg (AT) gmail (DOT) com>

Hi Bartek,

I'm currently using version 9.1.3 on windows but it will be used on a
fedora server afterwards if it changes anything.
I'm not sure I know the difference between anonymous block and ad hoc
query, but it's just a block I wrote in an admin page for a php based
website. It works well without the BEGIN and COMMIT and gives me back the
id but as soon as I add the transaction part I can't retrieve it.

Should I try in a function or stored procedure, I thought i'd start with
the simplier version first to debug because it has a lot of parameters.

Thank you for your time,
Guillaume

Le 18 avril 2012 21:45, Bartosz Dmytrak <bdmytrak (AT) gmail (DOT) com> a écrit :

2012/4/19 Guillaume Henriot <henriotg (AT) gmail (DOT) com>

platform doesn't matter, I asked about version, because You can use
anonymous block what is available since 9.0 AFAIK -
http://www.postgresql.org/docs/9.1/static/sql-do.html


1. use anonymous block like this one (with RETURNING):
DO
$$
DECLARE
"vParentRowId" "tblParent"."RowId"%TYPE;
BEGIN
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date) --that is because my test table strucutre
RETURNING "tblParent"."RowId" INTO "vParentRowId";

UPDATE "tblChild"
SET "ParentRowId" = "vParentRowId"
WHERE "RowId" = 123; --whatever ID You need;

RAISE NOTICE 'Inserted RowId: %', "vParentRowId";
END;
$$

please notice, there is not SELECT "vParentRowId", because this block is
treated as function BODY, so You would receive* "query has no destination
for result data"* error -
Trying use RETURN "vParentRowId" You will get error too, because DO returns
void.

2. use a stored procedure eg.:

CREATE OR REPLACE FUNCTION "setParent" (
IN "vParentRowValue" "tblParent"."RowValue"%TYPE,
IN "vChildRowId" "tblChild"."RowId"%TYPE,
OUT "ParentRowId" "tblParent"."RowId"%TYPE
)
RETURNS "tblParent"."RowId"%TYPE
AS
$BODY$
BEGIN
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING "tblParent"."RowId" INTO "setParent"."ParentRowId";

UPDATE "tblChild"
SET "ParentRowId" = "setParent"."ParentRowId"
WHERE "RowId" = "vChildRowId";

RETURN;
END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER STRICT;

3. use "WITH"
WITH inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
)
UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
RETURNING "ParentRowId"

This one is tricky - You will get "ParentRowId" only when row in tblChild
has been found and updated.

personally I prefer no.2 (function). Today I had long discussion with my
GUI guy about data logic, application logic and GUI logic. We agreed - data
logic should be kept as close to data as possible, business logic should be
kept in middleware (beans / servlets, etc) and GUI logic should be kept in
GUI. Of course, other people should have different point of view.

Regards,
Bartek

Hi,
there is option no. 4 (kind of extended WITH)

WITH
inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
),
updated_row AS (UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
)

SELECT "RowId" FROM inserted_row;

I missed it in previous post.
This could be part of function (SELECT statement should be modified) or
ad-hoc query.

Regards,
Bartek

Thank you so much for these examples, I tryed the 4th one for it's easiness
and it works perfectly ! I'm probably going to replace it with your no.2
example as soon as I have time as I do agree with you about data and
application logic.

Thanks again,
Guillaume

Le 21 avril 2012 10:26, Bartosz Dmytrak <bdmytrak (AT) gmail (DOT) com> a écrit :

Just one quick question again about my first problem, is that a limitation
that code between begin and commit can't send back the id, or was it just a
problem about my code ?

Le 23 avril 2012 17:17, Guillaume Henriot <henriotg (AT) gmail (DOT) com> a écrit :

2012/4/23 Guillaume Henriot <henriotg (AT) gmail (DOT) com>


I think there is a bug in Your code:
BEGIN;
INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id =
'some_row_id';
SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I looks like You are trying to select current value of other sequence. You
are trying to use table1_id_seq for update and parts_id_seq for select.

I tried similar code:
BEGIN;
INSERT INTO "tblParent" ("RowValue") VALUES ('2012-01-02'::date);
UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;

SELECT currval('"tblParent_RowId_seq"'::regclass);
COMMIT;

works for me, but select doesn't produce any output in pgAdmin, only a
message:
*Query result with 1 row discarded. - *is that Your exception?
*Query returned successfully with no result in 26 ms. *

AFAIK pgAdmin runs all statements in SQL window in one transaction, so
there is no need to put everything in BEGIN...COMMIT

I have tried this code line by line i psql and works fine too:
myDatabase=# BEGIN;
BEGIN
myDatabase=# INSERT INTO "tblParent" ("RowValue") VALUES
('2012-01-02'::date);
INSERT 0 1
myDatabase=# UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;
UPDATE 1
myDatabase=# SELECT currval('"tblParent_RowId_seq"'::regclass);
currval
---------
118
(1 row)

myDatabase=# COMMIT;
COMMIT


Regards,
Bartek

Ah yes, I did not see I left a mistake when I renamed my variables for
readability in my example. It should be 'table1_id_seq' instead of
'parts_id_seq' and it gives me the same exception you had. I understand you
don't need BEGIN/COMMIT in pgadmin, but I was just trying to test this
block as it did not send anything back if I pasted it in my php code too.

Anyway I was just curious, it's not that important, I'll start using your
examples and everything should be better

Guillaume

Le 24 avril 2012 20:46, Bartosz Dmytrak <bdmytrak (AT) gmail (DOT) com> a écrit :

I think this message is related with pgAdmin not with postgres - maybe
someone else could explain it.

Regards,
Bartek