Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot, bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon, which comes first ob_start or session, wwwxxx/58/2010

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