Re : Getting results from a dynamic query in PL/pgSQL
От | Alain RICHARD |
---|---|
Тема | Re : Getting results from a dynamic query in PL/pgSQL |
Дата | |
Msg-id | OF0A95D8B0.9FED0E25-ONC1256CBD.002991B6@cirso.fr обсуждение исходный текст |
Ответы |
Re: Getting results from a dynamic query in PL/pgSQL
(Johann Uhrmann <johann.uhrmann@xpecto.com>)
|
Список | pgsql-general |
Look at chapter 19.6.4 Looping through query results. i.e using plpgsql language: DECLARE myRecord RECORD BEGIN ... FOR myRecord IN EXECUTE ''SELECT col1, col2 FROM myTable '' LOOP -- statements using myRecord.col1 and myRecord.col2 ; END LOOP; ... END; FOR Alain RICHARD Département Etudes et Projets - Bureautique Collective CIPAM - Site de Montpellier johann.uhrmann@xpecto.com (Johann Uhrmann) Pour : pgsql-general@postgresql.org Envoyé par : cc : pgsql-general-owner+M36431@pos Objet : [GENERAL] Getting results from a dynamic query in PL/pgSQL tgresql.org 27/01/2003 16:01 Hello, is there a possibility to retrieve results from a dynamically generated query in PL/pgSQL? That would be like this: create function foo(text) returns text as ' DECLARE colname ALIAS FOR $1; result text; BEGIN SELECT INTO result colname from my_table; RETURN result; END; ' language 'plpgsql'; The actual column that is to be read from my_table should be passed as argument to the function. The example does not read the variable colname but tries to read a column named "colname" from my_table which is not how it should work. The keyword "EXECUTE" which could execute dynamic queries cannot return any value that a select statement would. (At least there is nothing about that in the docs.) In order to write a trigger function that reads column names out of a table and uses those column names afterwards, I need such a functionality. I tried a workaround by using EXECUTE to create a function that reads only the columns I need and then calling this dynamically generated function. However, that works only one time - then I get the following message: ERROR: plpgsql: cache lookup for proc 52118 failed plpgsql seems to cache the dynamically generated function even after it gets dropped or overwritten with "create or replace function...". Does anyone know how to use column names stored in variables within PL/pgSQL? Thank You in advance, Johann Uhrmann ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
В списке pgsql-general по дате отправления: