From: | Bartosz Dmytrak <bdmytrak(at)gmail(dot)com> |
---|---|
To: | Guillaume Henriot <henriotg(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Returning generated id after a transaction. |
Date: | 2012-04-20 21:08:18 |
Message-ID: | CAD8_UcYyT5qVsOTDSEKMgHgR4m0FYNoCXjWHFBY7v6ND1PDk0w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
2012/4/19 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.
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bartosz Dmytrak | 2012-04-21 08:26:31 | Re: Returning generated id after a transaction. |
Previous Message | johnmudd | 2012-04-20 02:31:10 | Re: Just trying to read first row in table based on index, pg scans and sorts instead? |