Thread: Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
On Aug 18, 9:23 pm, Lew <no...@lewscanon.com> wrote:
> Dale wrote:
> > Hi,
> > I've got some code which postgres 8.3.3 won't accept. Postgres
> > doesn't like the INTO clause on RETURNING INTO and I've tried
> > following the documentation.
>
> > UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
> > Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
> > inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
> > "RelatedID") RETURNING "Default" INTO oldDefault;
>
> > Does anyone have any ideas if the INTO clause actually works at all
> > for an UPDATE statement?
>
> <http://www.postgresql.org/docs/8.3/static/sql-update.html>
> does not list an INTO clause for UPDATE, and when you think about it, indeed
> such a clause doesn't make sense.
>
> --
> Lew
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
As per this documentation, you should be able to do it. It works for
the INSERT command, but not UPDATE. For the INSERT command, it makes
my code look neater and I image it's more efficient too.
Dale.
In response to Dale <harris_da@yahoo.com.au>:
> On Aug 18, 9:23 pm, Lew <no...@lewscanon.com> wrote:
> > Dale wrote:
> > > Hi,
> > > I've got some code which postgres 8.3.3 won't accept. Postgres
> > > doesn't like the INTO clause on RETURNING INTO and I've tried
> > > following the documentation.
> >
> > > UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
> > > Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
> > > inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
> > > "RelatedID") RETURNING "Default" INTO oldDefault;
> >
> > > Does anyone have any ideas if the INTO clause actually works at all
> > > for an UPDATE statement?
> >
> > <http://www.postgresql.org/docs/8.3/static/sql-update.html>
> > does not list an INTO clause for UPDATE, and when you think about it, indeed
> > such a clause doesn't make sense.
>
> http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
>
> As per this documentation, you should be able to do it. It works for
> the INSERT command, but not UPDATE. For the INSERT command, it makes
> my code look neater and I image it's more efficient too.
Is it possible that your UPDATE command is updating multiple rows? I
don't believe RETURNING will work on an UPDATE that touches more than 1
row.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
Re: Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
Alvaro Herrera
Date:
Bill Moran wrote: > Is it possible that your UPDATE command is updating multiple rows? I > don't believe RETURNING will work on an UPDATE that touches more than 1 > row. Hmm, why not? alvherre=> create table bill (a int, b text); CREATE TABLE alvherre=> insert into bill values (1, 'one'); INSERT 0 1 alvherre=> insert into bill values (2, 'two'); INSERT 0 1 alvherre=> update bill set b = a || ' ' || b returning b; b ------- 1 one 2 two (2 lignes) UPDATE 2 -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
In response to Alvaro Herrera <alvherre@commandprompt.com>: > Bill Moran wrote: > > > Is it possible that your UPDATE command is updating multiple rows? I > > don't believe RETURNING will work on an UPDATE that touches more than 1 > > row. > > Hmm, why not? Because a doc linked to earlier in this thread said so. Can't find it now, so it's possible that I misread it or was suffering from temporal insanity. oops ... -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Bill Moran wrote:
>> Is it possible that your UPDATE command is updating multiple rows? I
>> don't believe RETURNING will work on an UPDATE that touches more than 1
>> row.
> Hmm, why not?
plpgsql will reject UPDATE RETURNING INTO that returns more than one
row, since it hasn't got any place to put the additional data.
It wasn't clear to me whether the OP was even using plpgsql, but if he
was, the example should have worked. We'd need to see more detail to
guess what the real problem is.
regards, tom lane
Hi All, In the following documentation it advises that the UPDATE statement should be able to return a value into a variable in plpgsql. http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html It works for the INSERT command, but not UPDATE. For the INSERT command, it makes my code look neater and I image it's more efficient too. This time I am trying to UPDATE a field using a primary key, and return another field into a variable so that I can take necessary action if required later in the plpgsql script. I know that I can issue another SELECT query to retrieve the information, but I would have thought it would be a lot more efficient to return the value during the UPDATE. Regards, Dale. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane Sent: Wednesday, 20 August 2008 1:30 To: Alvaro Herrera Cc: Bill Moran; Dale; pgsql-general@postgresql.org Subject: Re: [GENERAL] Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully Alvaro Herrera <alvherre@commandprompt.com> writes: > Bill Moran wrote: >> Is it possible that your UPDATE command is updating multiple rows? I >> don't believe RETURNING will work on an UPDATE that touches more than 1 >> row. > Hmm, why not? plpgsql will reject UPDATE RETURNING INTO that returns more than one row, since it hasn't got any place to put the additional data. It wasn't clear to me whether the OP was even using plpgsql, but if he was, the example should have worked. We'd need to see more detail to guess what the real problem is. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
"Dale Harris" <itsupport@jonkers.com.au> writes: > In the following documentation it advises that the UPDATE statement should > be able to return a value into a variable in plpgsql. > http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html > It works for the INSERT command, but not UPDATE. As was already suggested, if you want help on this you are going to need to exhibit a complete problem case. Blanket assertions that are demonstrably false don't advance the cause for anyone. regards, tom lane
Dale Harris wrote:
> It works for the INSERT command, but not UPDATE. For the INSERT command, it
> makes my code look neater and I image it's more efficient too.
>
> This time I am trying to UPDATE a field using a primary key, and return
> another field into a variable so that I can take necessary action if
> required later in the plpgsql script. I know that I can issue another
> SELECT query to retrieve the information, but I would have thought it would
> be a lot more efficient to return the value during the UPDATE.
>
Works for me
test=# begin;
BEGIN
test=#
test=# create table foo(f1 int, f2 text);
CREATE TABLE
test=# insert into foo values(1, 'hi');
INSERT 0 1
test=# insert into foo values(2, 'hello');
INSERT 0 1
test=#
test=# create function bar(int,int) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# update foo set f1 = $2 where f1 = $1 returning * into r;
test$# raise notice '% %',r.f1,r.f2;
test$# return true;
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# create function bar1(text) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# for r in
test$# update foo set f2 = f2 || $1 returning *
test$# loop
test$# raise notice '% %',r.f1,r.f2;
test$# end loop;
test$# return true;
test$# end;
test$# $$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select * from bar(2,3);
NOTICE: 3 hello
bar
-----
t
(1 row)
test=#
test=# select * from bar1('!');
NOTICE: 1 hi!
NOTICE: 3 hello!
bar1
------
t
(1 row)
test=#
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
As per the original message:
>I've got some code which postgres 8.3.3 won't accept. Postgres doesn't like the INTO clause on RETURNING INTO and I've tried following the documentation.
>
>UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" = Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" = inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID, "RelatedID") RETURNING "Default" INTO oldDefault;
>
>Does anyone have any ideas if the INTO clause actually works at all for an UPDATE statement?
And documentation link which advises that the UPDATE statement should be able to return a value into a variable in plpgsql.
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
The query above is out of my plpgsql script and the WHERE clause selects an unique record. Therefore only 1 value should ever be returned. The point is that I don’t even get that far as the script fails to compile due to the INTO clause.
Regards,
Dale.
-----Original Message-----
From: Klint Gore [mailto:kgore4@une.edu.au]
Sent: Wednesday, 20 August 2008 12:47
To: Dale Harris
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] can't get UPDATE ... RETURNING ... INTO ... to compile successfully
Dale Harris wrote:
> It works for the INSERT command, but not UPDATE. For the INSERT command, it
> makes my code look neater and I image it's more efficient too.
>
> This time I am trying to UPDATE a field using a primary key, and return
> another field into a variable so that I can take necessary action if
> required later in the plpgsql script. I know that I can issue another
> SELECT query to retrieve the information, but I would have thought it would
> be a lot more efficient to return the value during the UPDATE.
>
Works for me
test=# begin;
BEGIN
test=#
test=# create table foo(f1 int, f2 text);
CREATE TABLE
test=# insert into foo values(1, 'hi');
INSERT 0 1
test=# insert into foo values(2, 'hello');
INSERT 0 1
test=#
test=# create function bar(int,int) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# update foo set f1 = $2 where f1 = $1 returning * into r;
test$# raise notice '% %',r.f1,r.f2;
test$# return true;
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# create function bar1(text) returns boolean as $$
test$# declare
test$# r record;
test$# begin
test$# for r in
test$# update foo set f2 = f2 || $1 returning *
test$# loop
test$# raise notice '% %',r.f1,r.f2;
test$# end loop;
test$# return true;
test$# end;
test$# $$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select * from bar(2,3);
NOTICE: 3 hello
bar
-----
t
(1 row)
test=#
test=# select * from bar1('!');
NOTICE: 1 hi!
NOTICE: 3 hello!
bar1
------
t
(1 row)
test=#
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
On Tue, Aug 19, 2008 at 9:51 PM, Dale Harris <itsupport@jonkers.com.au> wrote:
> As per the original message:
>>UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
>> Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
>> inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
>> "RelatedID") RETURNING "Default" INTO oldDefault;
This is called a code fragment. What people want to see here is a
self-contained example of it failing. Until you post one of those, no
one can troubleshoot it because it WORKS FOR THEM.
Create a test table
insert some data
create a plpgsql function
call that function and have it throw an error.
Post all of that here.
Dale Harris wrote:
>
> As per the original message:
>
> >I've got some code which postgres 8.3.3 won't accept. Postgres
> doesn't like the INTO clause on RETURNING INTO and I've tried
> following the documentation.
>
> >
>
> >UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
> Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
> inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
> "RelatedID") RETURNING "Default" INTO oldDefault;
>
> >
>
> >Does anyone have any ideas if the INTO clause actually works at all
> for an UPDATE statement?
>
> And documentation link which advises that the UPDATE statement should
> be able to *return a value into a variable* in plpgsql.
>
> http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html
>
> The query above is out of my plpgsql script and the WHERE clause
> selects an unique record. Therefore only 1 value should ever be
> returned. The point is that I don’t even get that far as the script
> fails to compile due to the INTO clause.
>
Show us the whole function - then we can try it and see where the
problem is. What is the actual message you get?
Are you sure you spelled entity right in inEnityID (need another T
perhaps)?
Does select version() actually say 8.3.3?
The statement you posted works for me.
test=# begin;
BEGIN
test=#
test=# create table "EntityRelation"
test-# ("EntityID" int,
test(# "Status" int,
test(# "Modified" timestamp,
test(# "ModifiedBy" text,
test(# "RelationID" int,
test(# "RelatedID" int,
test(# "Default" text);
CREATE TABLE
test=#
test=# insert into "EntityRelation"
test-# values (1,1,now(), 'me', 1,1,'hello');
INSERT 0 1
test=#
test=# create or replace function foo() returns boolean as $$
test$# declare
test$# oldDefault text;
test$# instatus int = 1;
test$# inRelationID int = 1;
test$# inRelatedID int = 1;
test$# inEnityID int = 1;
test$#
test$# begin
test$#
test$# UPDATE "EntityRelation"
test$# SET "Status" = inStatus,
test$# "Modified" = now(),
test$# "ModifiedBy" =current_user
test$# WHERE ("RelationID" = inRelationID)
test$# AND ("EntityID" = inEnityID)
test$# AND inRelatedID = "RelatedID"
test$# RETURNING "Default"
test$# INTO oldDefault;
test$#
test$# raise notice '%', oldDefault;
test$# return false;
test$#
test$# end;$$ language plpgsql volatile;
CREATE FUNCTION
test=#
test=# select foo();
NOTICE: hello
foo
-----
f
(1 row)
test=#
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
On Aug 20, 3:02 pm, scott.marl...@gmail.com ("Scott Marlowe") wrote:
> On Tue, Aug 19, 2008 at 9:51 PM, Dale Harris <itsupp...@jonkers.com.au> wrote:
> > As per the original message:
> >>UPDATE "EntityRelation" SET "Status" = inStatus, "Modified" =
> >> Session_TimeStamp(), "ModifiedBy" = UserID() WHERE ("RelationID" =
> >> inRelationID) AND ("EntityID" = inEnityID) AND IsEqual(inRelatedID,
> >> "RelatedID") RETURNING "Default" INTO oldDefault;
>
> This is called a code fragment. What people want to see here is a
> self-contained example of it failing. Until you post one of those, no
> one can troubleshoot it because it WORKS FOR THEM.
>
> Create a test table
> insert some data
> create a plpgsql function
> call that function and have it throw an error.
>
> Post all of that here.
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
I'm sorry about annoying everyone with this issue. I have just tried
to create a test table and function to demonstrate my issue, but all
now appears to be working. I put the original code back into the
function (including the spelling mistake) and it all compiles now
without any errors. I'm only new to PostreSQL and I'm finding it very
good, especially being fully open source and free. This afternoon I'm
starting to notice that pgAdmin III for Windows sometimes gives
erratic error messages when there is no error or the error is actually
different. I know this because when I recompile after moving the
cursor, the messages change. Maybe my issue was created by pgAdmin
rather than PostgreSQL.
Regards,
Dale.
Re: Re: can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
"Scott Marlowe"
Date:
On Wed, Aug 20, 2008 at 11:25 PM, Dale <harris_da@yahoo.com.au> wrote: > This afternoon I'm > starting to notice that pgAdmin III for Windows sometimes gives > erratic error messages when there is no error or the error is actually > different. I know this because when I recompile after moving the > cursor, the messages change. Maybe my issue was created by pgAdmin > rather than PostgreSQL. Glad you got your problem resolved. I have to admit I tend to mostly use psql to interact with postgres. Nothing against PGAdmin III, it's good software. I just tend to feel more comfy scripting things than clicking things. Whether you're using pgadmin III or psql, it's important that you have the right version. For pgadmin it's usually best to always have the latest version, and with psql the one that matches your db.
psql vs. pgadmin3 was:can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
Ivan Sergio Borgonovo
Date:
On Fri, 22 Aug 2008 18:34:53 -0600 "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > Glad you got your problem resolved. I have to admit I tend to > mostly use psql to interact with postgres. Nothing against > PGAdmin III, it's good software. I just tend to feel more comfy > scripting things than clicking things. Whether you're using > pgadmin III or psql, it's important that you have the right > version. For pgadmin it's usually best to always have the latest > version, and with psql the one that matches your db. Is there any secret I don't know? I mostly use pgadmin because: 1) It is easier to cut and paste code and results 2) It's easier to open several connections 3) you've the object structure to navigate But I miss psql auto completion. I know I can call an external editor that would make cut&paste easier in psql but I'll lose autocompletion and I've had some issues with pgadmin search&replace and regexp, so I definitively would enjoy psql more... but what about the rest? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Re: psql vs. pgadmin3 was:can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
Ivan Sergio Borgonovo
Date:
On Sat, 23 Aug 2008 11:41:41 -0600 "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > On Sat, Aug 23, 2008 at 10:28 AM, Ivan Sergio Borgonovo > <mail@webthatworks.it> wrote: > > On Fri, 22 Aug 2008 18:34:53 -0600 > > "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > > > >> Glad you got your problem resolved. I have to admit I tend to > >> mostly use psql to interact with postgres. Nothing against > >> PGAdmin III, it's good software. I just tend to feel more comfy > >> scripting things than clicking things. Whether you're using > >> pgadmin III or psql, it's important that you have the right > >> version. For pgadmin it's usually best to always have the > >> latest version, and with psql the one that matches your db. > > > > Is there any secret I don't know? > > I mostly use pgadmin because: > > 1) It is easier to cut and paste code and results > > I use /i /o a lot. poor man's cut and paste / multi-file buffer. > also highlight / middle button click. I just forgot that vim is vim: http://www.vim.org/scripts/script.php?script_id=356 Object Completion ---------------------------- dbext ties into Vim dictionary feature. You can complete table names, procedure names and view names using the i_CTRL-X_CTRL-K feature. Viewing Lists of Objects ------------------------------------ You can browse through the various objects in the database you are connected to and specify wildcard information. For example you can say, "Show me all tables beginning with 'ml_' ". These objects are currently supported: Tables, Procedures, Views, Columns (for a table). Trying to make it work. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Re: psql vs. pgadmin3 was:can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
"Scott Marlowe"
Date:
On Sat, Aug 23, 2008 at 10:28 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > On Fri, 22 Aug 2008 18:34:53 -0600 > "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > >> Glad you got your problem resolved. I have to admit I tend to >> mostly use psql to interact with postgres. Nothing against >> PGAdmin III, it's good software. I just tend to feel more comfy >> scripting things than clicking things. Whether you're using >> pgadmin III or psql, it's important that you have the right >> version. For pgadmin it's usually best to always have the latest >> version, and with psql the one that matches your db. > > Is there any secret I don't know? > I mostly use pgadmin because: > 1) It is easier to cut and paste code and results I use /i /o a lot. poor man's cut and paste / multi-file buffer. also highlight / middle button click. > 2) It's easier to open several connections I just use multiple term windows > 3) you've the object structure to navigate that's the main reason I'd find myself using it.
Re: psql vs. pgadmin3 was:can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
Marc Balmer
Date:
* Ivan Sergio Borgonovo wrote: > On Fri, 22 Aug 2008 18:34:53 -0600 > "Scott Marlowe" <scott.marlowe@gmail.com> wrote: > > > Glad you got your problem resolved. I have to admit I tend to > > mostly use psql to interact with postgres. Nothing against > > PGAdmin III, it's good software. I just tend to feel more comfy > > scripting things than clicking things. Whether you're using > > pgadmin III or psql, it's important that you have the right > > version. For pgadmin it's usually best to always have the latest > > version, and with psql the one that matches your db. > > Is there any secret I don't know? > I mostly use pgadmin because: > 1) It is easier to cut and paste code and results > 2) It's easier to open several connections > 3) you've the object structure to navigate The pgadmin3 version I use has some annoying bugs: If I want to add privileges to an object, it does not show me all roles in the corresponding drop down box. I am using version 1.8.2. Is that fixed in a newer version? > > But I miss psql auto completion. > > I know I can call an external editor that would make cut&paste > easier in psql but I'll lose autocompletion and I've had some issues > with pgadmin search&replace and regexp, so I definitively would > enjoy psql more... but what about the rest? > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: psql vs. pgadmin3 was:can't get UPDATE ... RETURNING ... INTO ... to compile successfully
From
Raymond O'Donnell
Date:
On 23/08/2008 18:56, Marc Balmer wrote: > The pgadmin3 version I use has some annoying bugs: If I want to > add privileges to an object, it does not show me all roles in > the corresponding drop down box. This isn't a bug - by default it shows group roles only. If you want to see users as well, you need to tell it to do so in the options. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------