Re: trigger - dynamic WHERE clause
От | Tarlika Elisabeth Schmitz |
---|---|
Тема | Re: trigger - dynamic WHERE clause |
Дата | |
Msg-id | 20110526224543.3c71066e@dick.coachhouse обсуждение исходный текст |
Ответ на | Re: trigger - dynamic WHERE clause (Pavel Stehule <pavel.stehule@gmail.com>) |
Список | pgsql-general |
On Sun, 22 May 2011 20:39:01 +0200 Pavel Stehule <pavel.stehule@gmail.com> wrote: >Hello > >2011/5/22 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>: >> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause >> || ' FOR UPDATE;'; >> >> I am generating the whereclause dynamically as the number of columns >> queried varies. >> >> Am I right in assuming that I cannot use EXECUTE ... USING in this >> scenario? >> > >why not? You can use it - just USING has a fixed numbers of >parameters, so you should to use a arrays. Thank you for your responses, Pavel, and for your excellent blog pages. Sorry, I am struggling with this a bit: Currently, I am producing the whereclause on a subset of columns: SELECT array_to_string (array( SELECT newrecord.key || ' = ' || quote_literal(newrecord.value) FROM (SELECT (each(hstore(NEW))).*) AS newrecord WHERE newrecord.key LIKE 'id%' ), ' AND ') INTO whereclause; That gives me, for example: SELECT 1 FROM test WHERE id1 = '26' AND id2 = 'name2' FOR UPDATE; In an attempt to use EXECUTE '...' USING, I tried to execute SELECT 1 FROM test WHERE id1 = $1 AND id2 = $2 FOR UPDATE; I produced an array of corresponding values: SELECT array( SELECT newrecord.value FROM (SELECT (each(hstore(NEW))).*) AS newrecord WHERE newrecord.key LIKE 'id%' ) INTO av; -- text array EXECUTE '...' USING av ==> ERROR: operator does not exist: integer = text[] -- Best Regards, Tarlika Elisabeth Schmitz
В списке pgsql-general по дате отправления: