Re: Set role dynamically from proc

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Set role dynamically from proc
Дата
Msg-id CAKFQuwZKAamsZdNMgmtFwT9Z9LFvCu6w7+O7kbFTXPMmeGHu-w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Set role dynamically from proc  (Durumdara <durumdara@gmail.com>)
Ответы Re: Set role dynamically from proc
Список pgsql-general
On Wed, Nov 22, 2017 at 7:52 AM, Durumdara <durumdara@gmail.com> wrote:
Now the "set role" uses the "variable name", and not the "value of the variable".

This is what I don't like in this lang. I need to write a special variable name to "force" to use it, and not other thing.
I don't know how to force the system to use my variable value, and not my variable name in the routines.
For example $var$, or <var>, or [var], etc.

I can generate "set role" with string concat, but in PGAdmin this script would be good.


​There are three kinds of "words" in an SQL command: keywords, identifiers, ​values.

SET ROLE TO davidj;

SET := keyword
ROLE := keyword
TO := keyword
davidj := *identifier*

In pl/pgsql variables can only replace values, not identifiers or keywords.  Since the variable in this case holds an identifier you must use dynamic SQL to execute the statement you want.

not tested but basically:

EXECUTE format('SET ROLE %I', variablename); --(that's a percent-eye)

More generally when using "PREPARE" at the SQL-level only values can be replaced with placeholders ($1, $2, etc...).  All pl/pgsql is doing when you use a variable is writing out a PREPARE variation of your command and the executing it with the variable values as parameter values.

Identifiers are any words that the system is going to lookup in a catalog (I may be over-simplifying a bit, and the converse, values are not resolved in a catalog, is generally but possibly not always, true).  Identifiers can always be double-quoted to avoid their case-folding behavior.

David J.

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

Предыдущее
От: Adam Brusselback
Дата:
Сообщение: [GENERAL] postgres_fdw & could not receive data from client:Connection reset by peer
Следующее
От: Magnus Hagander
Дата:
Сообщение: Re: duplicate primary key