Re: Returning generated id after a transaction.

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