Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: query fine in psql/pgAdmin III but error message when within plpgsqlfunction

Posted on 2010-08-20 10:44:33 by Jan-Peter.Seifert

Hello,

I thought I had some experience with plpgsql functions by now. However, w=
hen trying to do this query (generic column/table names):

UPDATE table_a cus SET spalte1 =3D ( SELECT spalte1 FROM table_a_copy cl =
WHERE cl.tabname =3D 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 EXECUT=
Eed.

I works without errors and seemingly the way I wanted when doing the upda=
te 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 publ=
ic so I'm using 'set search_path TO ...'.
--=20
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message

#2: Re: query fine in psql/pgAdmin III but error message whenwithin plpgsql function

Posted on 2010-08-20 11:27:18 by Jan-Peter.Seifert

Ouch. My mistake. :(

The copy had of course duplicate data sets, because I trapped errors duri=
ng its creation and because of the missing 'WHERE 1=3D0'.=20

Sorry,

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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message