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.
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