pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
query fine in psql/pgAdmin III but error message when within plpgsql function 2+ messages / 2 participants [nested] [flat]
* query fine in psql/pgAdmin III but error message when within plpgsql function @ 2010-08-20 08:44 Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> 0 siblings, 1 reply; 2+ messages in thread From: Jan-Peter Seifert @ 2010-08-20 08:44 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: query fine in psql/pgAdmin III but error message when within plpgsql function @ 2010-08-20 09:27 Jan-Peter.Seifert@gmx.de parent: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> 0 siblings, 0 replies; 2+ messages in thread From: Jan-Peter.Seifert@gmx.de @ 2010-08-20 09:27 UTC (permalink / raw) To: Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de>; pgsql-admin Ouch. My mistake. :( The copy had of course duplicate data sets, because I trapped errors during its creation and because of the missing 'WHERE 1=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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-20 09:27 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-08-20 08:44 query fine in psql/pgAdmin III but error message when within plpgsql function Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> 2010-08-20 09:27 ` Jan-Peter.Seifert@gmx.de
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