Content-Type: text/plain; charset="utf-8"
Date: Fri, 20 Aug 2010 10:44:33 +0200
From: "Jan-Peter Seifert" <Jan-Peter.Seifert@gmx.de>
Message-ID: <20100820084433.243470@gmx.net>
MIME-Version: 1.0
Subject: query fine in psql/pgAdmin III but error message when within plpgsql
	function
To: pgsql-admin@postgresql.org
Content-Transfer-Encoding: 8bit

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