Re: Returning generated id after a transaction.
| От | Bartosz Dmytrak |
|---|---|
| Тема | Re: Returning generated id after a transaction. |
| Дата | |
| Msg-id | CAD8_UcYyT5qVsOTDSEKMgHgR4m0FYNoCXjWHFBY7v6ND1PDk0w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Returning generated id after a transaction. (Guillaume Henriot <henriotg@gmail.com>) |
| Список | pgsql-novice |
2012/4/19 Guillaume Henriot <henriotg@gmail.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.
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
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
depends on needs You can:
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
В списке pgsql-novice по дате отправления: