Thread: query fine in psql/pgAdmin III but error message when within plpgsql function

query fine in psql/pgAdmin III but error message when within plpgsql function

From
"Jan-Peter Seifert"
Date:
Hello,

I thought I had some experience with plpgsql functions by now. However, when trying to do this query (generic
column/tablenames): 

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

Re: query fine in psql/pgAdmin III but error message when within plpgsql function

From
Jan-Peter.Seifert@gmx.de
Date:
Ouch. My mistake. :(

The copy had of course duplicate data sets, because I trapped errors during its creation and because of the missing
'WHERE1=0'.  

Sorry,

Peter
--
GMX DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für nur 19,99 ¿/mtl.!*
http://portal.gmx.net/de/go/dsl