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

Поиск
Список
Период
Сортировка
От Jan-Peter Seifert
Тема query fine in psql/pgAdmin III but error message when within plpgsql function
Дата
Msg-id 20100820084433.243470@gmx.net
обсуждение исходный текст
Ответы Re: query fine in psql/pgAdmin III but error message when within plpgsql function  (Jan-Peter.Seifert@gmx.de)
Список pgsql-admin
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Anj Adu
Дата:
Сообщение: Re: autovacuum check
Следующее
От: Jan-Peter.Seifert@gmx.de
Дата:
Сообщение: Re: query fine in psql/pgAdmin III but error message when within plpgsql function