can't get UPDATE ... RETURNING ... INTO ... to compile successfully

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&gt;
does not list an INTO clause for UPDATE, and when you think about it, indeed
such a clause doesn't make sense.

--
Lew

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&gt;
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&gt;
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

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

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

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.

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.

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

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.

* 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?

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

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