From: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>
To: pgsql-admin@postgresql.org
Subject: query fine in psql/pgAdmin III but error message when within plpgsql function
Date: Fri, 20 Aug 2010 10:44:33 +0200
Message-ID: <20100820084433.243470@gmx.net> (raw)
Hello,
I thought I had some experience with plpgsql functions by now. However, when trying to do this query (generic column/table names):
UPDATE table_a cus SET spalte1 = ( SELECT spalte1 FROM table_a_copy cl WHERE cl.tabname = cus.tabname );
from within a plpgsql funtion I get the error message:
'more than row returned by a subquery used as an expression'.
This even happens when I put the query into a string that I let be EXECUTEed.
I works without errors and seemingly the way I wanted when doing the update separately.
Before I create a copy of table_a with:
CREATE TABLE table_a_copy AS SELECT * FROM table_a;
Then I load the backed up data with COPY to table_a_copy.
Then I do the update(s).
Of course one could do the updates with a loop, but in my opinion that's rather inconvenient.
Any hints what the problem is?
Thank you very much,
Peter
P.S. Server version 8.3.11. The name of the schema is different from public so I'm using 'set search_path TO ...'.
--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Jan-Peter.Seifert@gmx.de
Subject: Re: query fine in psql/pgAdmin III but error message when within plpgsql function
In-Reply-To: <20100820084433.243470@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox